Postgres performs a Seq Scan instead of an Index Scan!

From: Jos van Roosmalen <josr(at)josr(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Postgres performs a Seq Scan instead of an Index Scan!
Date: 2004-10-26 16:12:36
Message-ID: 417E7774.3060005@josr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message James Robinson 2004-10-26 16:23:09 Re: Postgres performs a Seq Scan instead of an Index Scan!
Previous Message Greg Stark 2004-10-26 15:42:08 Re: plans for bitmap indexes?