Andrei Lepikhov <[email protected]> writes:
> Hmm, where is the evidence that your query uses theindex? Maybe the
> generic plan accidentally forces SeqScan?
Oh ... you're on to something. I think we all assumed that this was
an intermittent problem, but if it happens every time, I bet it's a
datatype mismatch issue. Observe:
postgres=# create table members(hid char(6) unique not null);
CREATE TABLE
postgres=# prepare p as select * from members where hid = '42';
PREPARE
postgres=# explain execute p;
QUERY PLAN
-------------------------------------------------------------------------------------
Index Only Scan using members_hid_key on members (cost=0.15..8.17 rows=1
width=10)
Index Cond: (hid = '42'::bpchar)
(2 rows)
postgres=# prepare p2(char) as select * from members where hid = $1;
PREPARE
postgres=# explain execute p2('42');
QUERY PLAN
-------------------------------------------------------------------------------------
Index Only Scan using members_hid_key on members (cost=0.15..8.17 rows=1
width=10)
Index Cond: (hid = '42'::bpchar)
(2 rows)
postgres=# prepare p3(text) as select * from members where hid = $1;
PREPARE
postgres=# explain execute p3('42');
QUERY PLAN
----------------------------------------------------------
Seq Scan on members (cost=0.00..42.10 rows=11 width=10)
Filter: ((hid)::text = '42'::text)
(2 rows)
If the $1 parameter is declared to be type "text" then it wins the
tug-of-war over which equals operator is used, and text-equals-text
does not match the bpchar index.
If it's not convenient to alter whatever aspect of the client logic is
causing that parameter to be marked as text, you could force the issue
by putting a cast into the text of the statement:
postgres=# prepare p4(text) as select * from members where hid = $1::char(6);
PREPARE
postgres=# explain execute p4('42');
QUERY PLAN
-------------------------------------------------------------------------------------
Index Only Scan using members_hid_key on members (cost=0.15..8.17 rows=1
width=10)
Index Cond: (hid = '42 '::character(6))
(2 rows)
Or better yet, avoid using char(n). It's a legacy type with no
real reason to exist, and it has semantic gotchas beyond this one.
varchar(n) is a much better idea.
regards, tom lane
