Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6UJKBf02215 for ; Mon, 30 Jul 2001 15:20:11 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6UJK6C21767; Mon, 30 Jul 2001 15:20:06 -0400 (EDT) To: "Chris Ruprecht" cc: "pgsql" Subject: Re: Big table - using wrong index - why? In-reply-to: <006401c11913$9a563ae0$5dd26383@corp.compucom.com> References: <006401c11913$9a563ae0$5dd26383@corp.compucom.com> Comments: In-reply-to "Chris Ruprecht" message dated "Mon, 30 Jul 2001 11:20:51 -0500" Date: Mon, 30 Jul 2001 15:20:06 -0400 Message-ID: <21764.996520806@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/387 X-Sequence-Number: 4201 "Chris Ruprecht" writes: > phones=# explain select * from phonelog where entity = '001' and pseq >= > 9120 and pseq <= 9123; > NOTICE: QUERY PLAN: > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > rows=607 width=137) Your problem is that pseq is of type int8 (bigint) but the constants 9120 and 9123 are of type int4 (integer). The system does not currently recognize cross-datatype comparisons as being compatible with indexes. To make the query indexable, you need to cast the constants to be the same type as the indexed column. Here's a simplified example: regression=# create table foo (f1 int8 primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# explain select * from foo where f1 = 42; NOTICE: QUERY PLAN: Seq Scan on foo (cost=0.00..22.50 rows=1 width=8) EXPLAIN regression=# explain select * from foo where f1 = 42::int8; NOTICE: QUERY PLAN: Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1 width=8) EXPLAIN Another way is to write the constants as unknown-type literals (ie, strings) and let the system decide that you meant to let them be int8: regression=# explain select * from foo where f1 = '42'; NOTICE: QUERY PLAN: Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1 width=8) EXPLAIN Variants of this problem arise with int2 and float4 columns, BTW. This is on the to-fix list... see the pghackers list archives for excruciatingly long discussions about how to fix it without breaking other cases... regards, tom lane