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

Поиск
Список
Период
Сортировка
От Kim Johan Andersson
Тема Partial index on enum type is not being used, type issue?
Дата
Msg-id 9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk
обсуждение исходный текст
Ответы Re: Partial index on enum type is not being used, type issue?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Arturas Mazeika
Дата:
Сообщение: Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Partial index on enum type is not being used, type issue?