From: Tom Lane <tgl@sss.pgh.pa.us>
I was under impression that when max(<primary key>) is called, it should
just take the value from the index. I believe it should not do any kind
of
scan. But, in fact, it scans the table.
You are mistaken. Postgres has no idea that min() and max() have any
semantics that have anything to do with indexes. I would like to see
that optimization myself, but it's not a particularly easy thing to add
given the system structure and the emphasis on datatype extensibility.
it's a show stopper for us.
You might be able to hack around the issue with queries like

SELECT x FROM table ORDER BY x LIMIT 1;
SELECT x FROM table ORDER BY x DESC LIMIT 1;
It is a real show stopper. No luck completely, the indexes are ignored:

*************************************************************
[PostgreSQL 6.5.0 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1]

bars=> create index bars_id on itemsbars(id);
CREATE
bars=> explain select id from itemsbars order by id limit 1;
NOTICE: QUERY PLAN:

Sort (cost=44404.41 rows=969073 width=4)
-> Seq Scan on itemsbars (cost=44404.41 rows=969073 width=4)

EXPLAIN
bars=> \d itemsbars
Table = itemsbars
+--------------------+----------------------------------+-------+
Field | Type | Length|
+--------------------+----------------------------------+-------+
id | int4 not null default nextval('" | 4 |
itemid | int4 not null | 4 |
interv | int4 not null | 4 |
stats | datetime not null | 8 |
endts | datetime not null | 8 |
isactive | bool not null | 1 |
opn | float8 not null | 8 |
high | float8 not null | 8 |
low | float8 not null | 8 |
cls | float8 not null | 8 |
vol | int4 not null | 4 |
+--------------------+----------------------------------+-------+
Indices: bars_complex2
bars_endts
bars_id
bars_interv
bars_itemid
bars_stats
itemsbars_pkey

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 5 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 27, '99 at 2:46p
activeSep 15, '99 at 1:37a
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase