[HACKERS] Postgres performs a Seq Scan instead of an Index Scan!

[Jos van Roosmalen]

Hello,
I have a little question. Why performs Postgresql a Seq. Scan in the 
next Select statement instead of a Index Read?

I have an index on (ATTR1,ATTR2,ATTR3), so why is postgresql not 
performing a Index Keyed Read in the SELECT?

I agree that the tables are empty so maybe this influence the decision 
to do a Seq scan, but my app use a DB with arround 100.000 records and 
it still does a seq. scan.

CREATE TABLE TESTTABLE (ATTR1 INT8,ATTR2 INT4,ATTR3 TIMESTAMP);
CREATE UNIQUE INDEX TESTINDEX ON TESTTABLE(ATTR1,ATTR2,ATTR3);
EXPLAIN SELECT * FROM TESTTABLE WHERE ATTR1=1 AND ATTR2=2 AND 
ATTR3='2004-01-01';

Result in:
                                               QUERY 
PLAN                                               
----------------------------------------------------------------------------------------------------------
Seq Scan on testtable  (cost=0.00..27.50 rows=1 width=20)
  Filter: ((attr1 = 1) AND (attr2 = 2) AND (attr3 = '2004-01-01 
00:00:00'::timestamp without time zone))
(2 rows)

If I add a INDEXHELPER it helps a bit. But it's not a 100% Index Scan.
CREATE TABLE TESTTABLE (ATTR1 INT8,ATTR2 INT4,ATTR3 TIMESTAMP);
CREATE UNIQUE INDEX TESTINDEX ON TESTTABLE(ATTR1,ATTR2,ATTR3);
CREATE INDEX INDEXHELPER ON TESTTABLE(ATTR3);
EXPLAIN SELECT * FROM TESTTABLE WHERE ATTR1=1 AND ATTR2=2 AND 
ATTR3='2004-01-01';

                                 QUERY 
PLAN                                  
-------------------------------------------------------------------------------
Index Scan using indexhelper on testtable  (cost=0.00..17.09 rows=1 
width=20)
  Index Cond: (attr3 = '2004-01-01 00:00:00'::timestamp without time zone)
  Filter: ((attr1 = 1) AND (attr2 = 2))
(3 rows)

Changing from TIMESTAMP to DATE don't help (I am not using the time 
component in my app):

CREATE TABLE TESTTABLE (ATTR1 INT8,ATTR2 INT4,ATTR3 DATE);
CREATE UNIQUE INDEX TESTINDEX ON TESTTABLE(ATTR1,ATTR2,ATTR3);
EXPLAIN SELECT * FROM TESTTABLE WHERE ATTR1=1 AND ATTR2=2 AND 
ATTR3='2004-01-01';

                               QUERY PLAN                               
--------------------------------------------------------------------------
Seq Scan on testtable  (cost=0.00..27.50 rows=1 width=16)
  Filter: ((attr1 = 1) AND (attr2 = 2) AND (attr3 = '2004-01-01'::date))
(2 rows)

Thanks in Advance,
Jos
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html