From: Kim Johan Andersson <kimjand@kimmet.dk>
To: pgsql-performance@lists.postgresql.org
Subject: Partial index on enum type is not being used, type issue?
Date: Mon, 27 Sep 2021 22:02:49 +0200
Message-ID: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk> (raw)
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
SELECT version();
CREATE TYPE type_table_test_enum AS ENUM ( 'Ole', 'Bole', 'Skole' );
CREATE TABLE table_test_enum ( val type_table_test_enum NOT NULL, dat date );
CREATE UNIQUE INDEX ON table_test_enum ( val ) WHERE val = 'Ole' AND dat IS NULL;
INSERT INTO table_test_enum SELECT 'Ole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum SELECT 'Bole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum SELECT 'Skole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum VALUES ( 'Ole', NULL );
VACUUM (analyze) table_test_enum;
EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM table_test_enum WHERE val = 'Ole' and dat IS NULL;
PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val = $1::type_table_test_enum AND dat IS NULL;
PREPARE qry2(type_table_test_enum) AS select val FROM table_test_enum WHERE val = $1 AND dat IS NULL;
PREPARE qry3(unknown) AS SELECT val FROM table_test_enum WHERE val = $1 AND dat IS NULL;
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry1('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry2('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry3('Ole');
DEALLOCATE qry1;
DEALLOCATE qry2;
DEALLOCATE qry3;
DROP TABLE table_test_enum;
DROP TYPE type_table_test_enum;
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
CREATE TYPE
CREATE TABLE
CREATE INDEX
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1
VACUUM
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)
PREPARE
PREPARE
PREPARE
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)
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.023..0.026 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.256 ms
Execution Time: 0.054 ms
(5 rows)
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.019..0.023 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.259 ms
Execution Time: 0.051 ms
(5 rows)
DEALLOCATE
DEALLOCATE
DEALLOCATE
DROP TABLE
DROP TYPE
Attachments:
[text/plain] enumTest.sql (1.3K, ../9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk/2-enumTest.sql)
download | inline:
SELECT version();
CREATE TYPE type_table_test_enum AS ENUM ( 'Ole', 'Bole', 'Skole' );
CREATE TABLE table_test_enum ( val type_table_test_enum NOT NULL, dat date );
CREATE UNIQUE INDEX ON table_test_enum ( val ) WHERE val = 'Ole' AND dat IS NULL;
INSERT INTO table_test_enum SELECT 'Ole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum SELECT 'Bole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum SELECT 'Skole', current_date FROM generate_series( 1, 1000 );
INSERT INTO table_test_enum VALUES ( 'Ole', NULL );
VACUUM (analyze) table_test_enum;
EXPLAIN (analyze, costs, buffers, verbose) SELECT val FROM table_test_enum WHERE val = 'Ole' and dat IS NULL;
PREPARE qry1(varchar) AS SELECT val FROM table_test_enum WHERE val = $1::type_table_test_enum AND dat IS NULL;
PREPARE qry2(type_table_test_enum) AS select val FROM table_test_enum WHERE val = $1 AND dat IS NULL;
PREPARE qry3(unknown) AS SELECT val FROM table_test_enum WHERE val = $1 AND dat IS NULL;
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry1('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry2('Ole');
EXPLAIN (analyze, costs, buffers, verbose) EXECUTE qry3('Ole');
DEALLOCATE qry1;
DEALLOCATE qry2;
DEALLOCATE qry3;
DROP TABLE table_test_enum;
DROP TYPE type_table_test_enum;
[text/plain] enumTest.log (2.4K, ../9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk/3-enumTest.log)
download | inline:
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
CREATE TYPE
CREATE TABLE
CREATE INDEX
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
INSERT 0 1
VACUUM
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)
PREPARE
PREPARE
PREPARE
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)
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.023..0.026 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.256 ms
Execution Time: 0.054 ms
(5 rows)
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.019..0.023 rows=1 loops=1)
Output: val
Heap Fetches: 0
Planning Time: 0.259 ms
Execution Time: 0.051 ms
(5 rows)
DEALLOCATE
DEALLOCATE
DEALLOCATE
DROP TABLE
DROP TYPE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kimjand@kimmet.dk, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index on enum type is not being used, type issue?
In-Reply-To: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox