Thread: Postgres performs a Seq Scan instead of an Index Scan!

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

On Oct 26, 2004, at 12:12 PM, Jos van Roosmalen wrote:

> ATTR1 INT8

Looks like your column is int8, yet your query is sending in an int4. 
Therefore the index is not used. This is fixed in PG 8.0. In the mean 
time, you can:
SELECT * FROM TESTTABLE WHERE ATTR1=1::INT8 ...

which explicitly casts the literal int4 to an int8, making the int8 
column index useable.

----
James Robinson
Socialserve.com

On Tue, 26 Oct 2004 18:12:36 +0200, Jos van Roosmalen wrote:
> 
> I have a little question. Why performs Postgresql a Seq. Scan in the
> next Select statement instead of a Index Read?

That is a FAQ: http://www.postgresql.org/docs/faqs/FAQ.html#4.8


Please direct any further questions of this nature that are not
covered in the FAQ or the documentation to the pgsql-performance list.

Jochem

On Tue, Oct 26, 2004 at 06:12:36PM +0200, Jos van Roosmalen wrote:
> 
> 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';


try:
explain select * from testtable where attr1=1::int8 and attr2=2
and attr3='2004-01-01';

Or change the int8 to bigint.

Isn't this in some FAQ yet?

It will not do an index scan if the types don't match.


Kurt