From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres chooses slow query plan from time to time
Date: Wed, 15 Sep 2021 08:16:52 -0400
Message-ID: <973795.1631708212@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOQPKatmYmcTPBz9x00oS15FZVPGDf4X2_soQ9oQ9WY0z28z6Q@mail.gmail.com>
References: <CAOQPKavhdTrgQo8Uss0M+K0Du+_WYmdoGyMpC0hJQkKM+-X25Q@mail.gmail.com>
<31c01752-c4d6-7751-9d25-b1fac8f3a7aa@enterprisedb.com>
<CAOQPKasCkSAW6FTt_iCY1Ts8tUgT+mf+jro46QXE50eVAQ6Pvg@mail.gmail.com>
<CAMkU=1zp56=kfsQMxfrEyVn4sxOY1HH3GPoANN5rGKUHvW4S7w@mail.gmail.com>
<CAOQPKati1jk6PniYNcodoC6T=4+ZrWxnmoVHOJe92-BpMsRHmg@mail.gmail.com>
<589350.1631628930@sss.pgh.pa.us>
<CAOQPKauv_5UnjzQuQRovuHJQ45JpmWcoXHEU5WsWJ5HtgtBbGg@mail.gmail.com>
<632224.1631634441@sss.pgh.pa.us>
<CAOQPKatmYmcTPBz9x00oS15FZVPGDf4X2_soQ9oQ9WY0z28z6Q@mail.gmail.com>
Kristjan Mustkivi <sonicmonkey@gmail.com> writes:
> Both are of type varchar(30).
Ah, right, you showed that back at the top of the thread.
> So is this something odd: Filter: (((product_code)::text = ($1)::text)
> AND ((balance_type)::text = ($4)::text)) ?
Yes, that is very darn odd. When I try this I get:
regression=# create table foo(f1 varchar(30), f2 int, primary key (f2,f1));
CREATE TABLE
regression=# explain select * from foo where f2 = 11 and f1 = 'bar';
QUERY PLAN
--------------------------------------------------------------------------
Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=37)
Index Cond: ((f2 = 11) AND (f1 = 'bar'::text))
(2 rows)
regression=# explain select * from foo where f2 = 11 and f1::text = 'bar';
QUERY PLAN
--------------------------------------------------------------------------
Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=37)
Index Cond: ((f2 = 11) AND (f1 = 'bar'::text))
(2 rows)
regression=# prepare p as select * from foo where f2 = $1 and f1 = $2;
PREPARE
regression=# explain execute p(11,'bar');
QUERY PLAN
--------------------------------------------------------------------------
Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=37)
Index Cond: ((f2 = 11) AND (f1 = 'bar'::text))
(2 rows)
-- repeat a few times till it switches to a generic plan ...
regression=# explain execute p(11,'bar');
QUERY PLAN
--------------------------------------------------------------------------
Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=37)
Index Cond: ((f2 = $1) AND (f1 = $2))
(2 rows)
Note the lack of any visible cast on the varchar column, in each one of
these queries, even where I tried to force one to appear. There is
something happening in your database that is not happening in mine.
My mind is now running to the possibility that you've got some extension
that creates an "=" operator that is capturing the syntax.
regards, tom lane
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sonicmonkey@gmail.com, jeff.janes@gmail.com, tomas.vondra@enterprisedb.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres chooses slow query plan from time to time
In-Reply-To: <973795.1631708212@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox