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

I have run into the following issue: A table contains an enum column, 
and a partial unique index is available on the table.
This index contains exactly the row I am querying for. Unfortunately the 
index is not always used, and I don't really understand why.

The attachments enumTest.sql shows the script reproducing the behaviour, 
and the enumTest.log shows the result when running on PostgreSQL 13.4.
There doesn't seem to be any difference from PG11 through 14-RC1.

First off I tried to do a simple test to see if the index was being used:

EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM 
table_test_enum WHERE val = 'Ole' and dat IS NULL;
                          QUERY PLAN
------------------------------------------------------------------------
  Index Only Scan using table_test_enum_val_idx on 
public.table_test_enum  (cost=0.12..4.14 rows=1 width=4) (actual 
time=0.014..0.016 rows=1 loops=1)
    Output: val
    Heap Fetches: 0
  Planning Time: 0.436 ms
  Execution Time: 0.048 ms
(5 rows)

All is fine, but in my application the query is executed as a prepared 
statement, using a varchar parameter:

PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val = 
$1::type_table_test_enum AND dat IS NULL;
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry1('Ole');
                                                    QUERY PLAN
----------------------------------------------------------------------
  Seq Scan on public.table_test_enum  (cost=0.00..66.52 rows=1 width=4) 
(actual time=1.131..1.133 rows=1 loops=1)
    Output: val
    Filter: ((table_test_enum.dat IS NULL) AND (table_test_enum.val = 
('Ole'::cstring)::type_table_test_enum))
    Rows Removed by Filter: 3000
  Planning Time: 0.261 ms
  Execution Time: 1.162 ms
(6 rows)

To my surprise the planner decides not to use the index. This is the 
part I do not understand. Why is the result different here?
There is obviously a cast that happens before the equality, does the 
cstring cast have anything to do with this? Hints are welcome!

So I tried to prepare a statement with a parameter of type 
type_table_test_enum instead, unsurprisingly, this works fine. No 
mentioning of cstring in the plan.
I also tried to use a parameter of unknown type, which I would think 
would be analogous to the first statement with the literal 'Ole', and 
that looks fine.
So why is the varchar version not using the index?
Any thoughs on this?

    Regards,
        Kim Johan Andersson

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