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

From: Kim Johan Andersson <kimjand(at)kimmet(dot)dk>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Partial index on enum type is not being used, type issue?
Date: 2021-09-27 20:02:49
Message-ID: 9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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

Attachment Content-Type Size
enumTest.sql text/plain 1.3 KB
enumTest.log text/plain 2.4 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2021-09-27 20:46:30 Re: Partial index on enum type is not being used, type issue?
Previous Message Arturas Mazeika 2021-09-27 19:56:12 Re: hashjoins, index loops to retrieve pk/ux constrains in pg12