Re: Partial index on enum type is not being used, type issue?

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