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