From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> |
Cc: | Robert Wille <rwille(at)iarchives(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why won't the query planner use my index? |
Date: | 2002-03-28 07:05:05 |
Message-ID: | 23830.1017299105@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> On Wed, 2002-03-27 at 23:46, Robert Wille wrote:
>> test=# explain select max(id) from a;
>> This one is quite baffling. All the DB needs to do is look at the end of the primary key index.
> Postgres will never use an index for this kind of query. If you need
> this kind of functionality, perhaps you can manipulate the sequence
> directly, using nextval() or currval().
The traditional answer is
select id from a order by id desc limit 1;
which will give an indexscan-based plan in recent releases. Since this
is more functional than a max() query (because you can get at all the
columns of the row containing the maximum ID value, not only the max
itself), there's not been a huge amount of interest in teaching the
planner that there might be some relationship between btree indexes and
max/min aggregates. We do regularly get razzed by people who think that
such a relationship is "obvious" ... but I like Postgres' black-box
approach to aggregates, and am not eager to break it for little or no
gain in functionality.
Wille's test case does seem to expose some problems in current sources:
I notice that a plain "ANALYZE A" produces a ridiculously low reltuples
estimate. I think this might be because the update sequence in his
script ends up with the first pages of the table completely empty ---
that seems to be causing ANALYZE to do the wrong thing. Too tired to
look at it more tonight, though.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Medi Montaseri | 2002-03-28 07:21:01 | An Invoicing db design, how would you do it |
Previous Message | Thomas T. Thai | 2002-03-28 06:53:32 | PHP, PgSQL, stale Persistent connection |