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?
In-reply-to: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
References: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
Comments: In-reply-to Kim Johan Andersson <kimjand@kimmet.dk>
	message dated "Mon, 27 Sep 2021 22:02:49 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2770838.1632775590.1@sss.pgh.pa.us>
Date: Mon, 27 Sep 2021 16:46:30 -0400
Message-ID: <2770839.1632775590@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2770839.1632775590%40sss.pgh.pa.us>
Precedence: bulk

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