From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
cc: Jeff Janes <jeff.janes@gmail.com>,
        Tomas Vondra <tomas.vondra@enterprisedb.com>,
        Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgres chooses slow query plan from time to time
In-reply-to: 
 <CAOQPKauv_5UnjzQuQRovuHJQ45JpmWcoXHEU5WsWJ5HtgtBbGg@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>
Comments: In-reply-to Kristjan Mustkivi <sonicmonkey@gmail.com>
	message dated "Tue, 14 Sep 2021 18:36:45 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <632223.1631634441.1@sss.pgh.pa.us>
Date: Tue, 14 Sep 2021 11:47:21 -0400
Message-ID: <632224.1631634441@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/632224.1631634441%40sss.pgh.pa.us>
Precedence: bulk

Kristjan Mustkivi <sonicmonkey@gmail.com> writes:
>>> Filter: (((product_code)::text = ($1)::text) AND
>>> ((balance_type)::text = ($4)::text))

> But the Primary Key is defined as btree (cage_code, cage_player_id,
> product_code, balance_type, version) so this should be exactly that
> (apart from the extra "version" column).

Oh, interesting.  So this is really a datatype mismatch problem.
I'd wondered idly why you were getting the explicit casts to text
in these conditions, but now it seems that that's key to the
problem: the casts prevent these clauses from being matched to
the index.  What are the declared data types of product_code
and balance_type?  And of the parameters they're compared to?

			regards, tom lane