Postgres performs a Seq Scan instead of an Index Scan!

Поиск
Список
Период
Сортировка
От Jos van Roosmalen
Тема Postgres performs a Seq Scan instead of an Index Scan!
Дата
Msg-id 417E7774.3060005@josr.org
обсуждение исходный текст
Ответы Re: Postgres performs a Seq Scan instead of an Index Scan!  (James Robinson <jlrobins@socialserve.com>)
Re: Postgres performs a Seq Scan instead of an Index Scan!  (Jochem van Dieten <jochemd@gmail.com>)
Re: Postgres performs a Seq Scan instead of an Index Scan!  (Kurt Roeckx <Q@ping.be>)
Список 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=1width=16)  Filter: ((attr1 = 1) AND (attr2 = 2) AND (attr3 = '2004-01-01'::date))
 
(2 rows)

Thanks in Advance,

Jos



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: plans for bitmap indexes?
Следующее
От: James Robinson
Дата:
Сообщение: Re: Postgres performs a Seq Scan instead of an Index Scan!