Thread

1. Not using indexes in WHERE clauses

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-19T12:42:15Z

   Marcin Zukowski (eru@mimuw.edu.pl) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   Not using indexes in WHERE clauses
   
   Long Description
   PGSQL doesn't use indexes in WHERE clauses.
   
   Tested on version 7.0.3:
   PostgreSQL 7.0.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
   
   explain query from example returns:
   Seq Scan on tab4  (cost=0.00..3576.00 rows=99999 width=8)
   
   It DID work on:
   PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
   
   where explain query returned:
   Index Scan using tab4_pkey on tab4  (cost=2565.67 rows=66667 width=8)
   
   Vacuuming didn't help.
   So, it's quite similar to the bug:
   Bug Report #10 from 2000-08-23 17:53:13-04
   but solution (changing to int4 and vacuuming) didn't help.
   
   Sample Code
   CREATE TABLE tab4 (
     id INT4 PRIMARY KEY,
     val INT4
   );
   -- Load 200000 records with values ((0,0),(1,1),... :) )
   COPY tab4 FROM '/home/postgres/pg/tab4.txt';
   CREATE INDEX tab4_id2 ON tab4 (val);
   
   -- THIS IS THE POINT
   explain SELECT * FROM tab4 WHERE id > 100000 ;
   
   
   
   No file was uploaded with this report
   
   
   

2. Re: Not using indexes in WHERE clauses

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-19T15:47:26Z

   Using a seqscan to retrieve half of the entries in a table is not a bug,
   it is correct behavior.
   
   Your test case might make you think otherwise, but that's because your
   test data is artificially well-ordered.  Try the two methods with the
   same data loaded in a random order ...
   
   			regards, tom lane