Обсуждение: SELECT min(id) FROM test; uses seqscan

Nick Gazaloff (nick@club.pyat.ru) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
SELECT min(id) FROM test; uses seqscan

Long Description
SELECT min(id) FROM test; uses sequential scan even if an index on "id" exists. VACUUM ANALYZE doesn't help.


Sample Code


No file was uploaded with this report

At 09:08 2/05/01 -0400, pgsql-bugs@postgresql.org wrote:
>SELECT min(id) FROM test; uses sequential scan even if an index on "id"
exists. VACUUM ANALYZE doesn't help.
>

This is a known stupidity of PG, and will probably be fixed in a relatively
distant future release (when index entries are updated to match row
status). The simple workaround is:

    Select id from test order by id as limit 1;


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
>> SELECT min(id) FROM test; uses sequential scan even if an index on "id"
>> exists. VACUUM ANALYZE doesn't help.

> This is a known stupidity of PG, and will probably be fixed in a relatively
> distant future release (when index entries are updated to match row
> status). The simple workaround is:

>     Select id from test order by id as limit 1;

Keeping status markers in index entries really doesn't have much of
anything to do with it.  The hard part is teaching the planner to
generate a completely different kind of plan for some aggregates
(viz min/max) than it does for others --- but only when an index of the
right type is available.  While it might not be too bad in DBMSes that
have a small, fixed set of aggregate functions, PG's extensible set
of aggregates and datatypes (not to mention index types) makes this
rather difficult.  We need to design some sort of tabular representation
of when and how to generate a specialized plan.

            regards, tom lane

> SELECT min(id) FROM test; uses sequential scan even if an index on "id" exists. VACUUM ANALYZE doesn't help.

This is not a bug.  In the current implementation, min and max cannot make
use of an index directly.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter