public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: Lillian Berry <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Slow queries on simple index
Date: Sat, 17 Jan 2026 16:10:45 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
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
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Slow queries on simple index
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox