[HACKERS] 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=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
Re: [HACKERS] Postgres performs a Seq Scan instead of an Index Scan!
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Postgres performs a Seq Scan instead of an Index Scan!
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Postgres performs a Seq Scan instead of an Index Scan!
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])