From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kim Johan Andersson <kimjand@kimmet.dk>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partial index on enum type is not being used, type issue?
Date: Mon, 27 Sep 2021 16:46:30 -0400
Message-ID: <2770839.1632775590@sss.pgh.pa.us> (raw)
In-Reply-To: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
References: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
Kim Johan Andersson <kimjand@kimmet.dk> writes:
> [ uses partial index: ]
> EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM
> table_test_enum WHERE val = 'Ole' and dat IS NULL;
>
> [ doesn't: ]
> PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val =
> $1::type_table_test_enum AND dat IS NULL;
There's no actual cast from varchar to that enum type. The system
is letting you get away with it anyway, by applying what's called a
CoerceViaIO cast --- which means convert the varchar to a simple
string (cstring) and then apply enum_in().
Unfortunately for you, enum_in() is marked stable not immutable
(probably on the grounds that it depends on catalog contents) so the
expression isn't reduced to a plain constant during constant-folding
and thus fails to match the partial index's WHERE clause.
In the first case, 'Ole' is taken as a constant of type
type_table_test_enum right off the bat, as was the same constant
in the index's WHERE clause, so everything matches fine.
(This seems a little inconsistent now that I think about it ---
if it's okay to fold the literal to an enum constant at parse time,
why can't we do the equivalent at plan time? But these rules have
stood for a good while so I'm hesitant to change them.)
Anyway, the recommendable solution is the one you already found:
declare the PREPARE's argument as type_table_test_enum not varchar.
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, kimjand@kimmet.dk, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index on enum type is not being used, type issue?
In-Reply-To: <2770839.1632775590@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