Re: Postgres chooses slow query plan from time to time

Hello!

Both are of type varchar(30).

So is this something odd: Filter: (((product_code)::text = ($1)::text)
AND ((balance_type)::text = ($4)::text)) ?

But why does it do the type-cast if both product_code and balance_type
are of type text (although with constraint 30) and the values are also
of type text?

Best regards,

Kristjan

On Tue, Sep 14, 2021 at 6:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Kristjan Mustkivi <sonicmonkey(at)gmail(dot)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

--
Kristjan Mustkivi

Email: kristjan(dot)mustkivi(at)gmail(dot)com