Thread

1. SELECT min(id) FROM test; uses seqscan

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-02T13:08:55Z

   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
   
   
   

2. Re: SELECT min(id) FROM test; uses seqscan

   Philip Warner <pjw@rhyme.com.au> — 2001-05-03T02:50:05Z

   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   |/
   
   

3. Re: SELECT min(id) FROM test; uses seqscan

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-03T04:04:52Z

   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
   
   

4. Re: SELECT min(id) FROM test; uses seqscan

   Peter Eisentraut <peter_e@gmx.net> — 2001-05-03T15:44:24Z

   > 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