From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kim Johan Andersson <kimjand(at)kimmet(dot)dk> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Partial index on enum type is not being used, type issue? |
Date: | 2021-09-27 20:46:30 |
Message-ID: | 2770839.1632775590@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Kim Johan Andersson <kimjand(at)kimmet(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | ldh@laurent-hasson.com | 2021-09-28 04:23:05 | RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 |
Previous Message | Kim Johan Andersson | 2021-09-27 20:02:49 | Partial index on enum type is not being used, type issue? |