To: pgsql-performance@lists.postgresql.org
From: Kim Johan Andersson <kimjand@kimmet.dk>
Subject: Partial index on enum type is not being used, type issue?
Message-ID: <9a51090e-e075-4f2f-e3a6-55ed4359a357@kimmet.dk>
Date: Mon, 27 Sep 2021 22:02:49 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------D8B0B3455A30F70A23DC82FB"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/9a51090e-e075-4f2f-e3a6-55ed4359a357%40kimmet.dk>
Precedence: bulk

This is a multi-part message in MIME format.
--------------D8B0B3455A30F70A23DC82FB
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


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

--------------D8B0B3455A30F70A23DC82FB
Content-Type: text/plain; charset=UTF-8;
 name="enumTest.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="enumTest.sql"

U0VMRUNUIHZlcnNpb24oKTsNCg0KQ1JFQVRFIFRZUEUgdHlwZV90YWJsZV90ZXN0X2VudW0g
QVMgRU5VTSAoICdPbGUnLCAnQm9sZScsICdTa29sZScgKTsNCkNSRUFURSBUQUJMRSB0YWJs
ZV90ZXN0X2VudW0gKCB2YWwgdHlwZV90YWJsZV90ZXN0X2VudW0gTk9UIE5VTEwsIGRhdCBk
YXRlICk7DQpDUkVBVEUgVU5JUVVFIElOREVYIE9OIHRhYmxlX3Rlc3RfZW51bSAoIHZhbCAp
IFdIRVJFIHZhbCA9ICdPbGUnIEFORCBkYXQgSVMgTlVMTDsNCg0KSU5TRVJUIElOVE8gdGFi
bGVfdGVzdF9lbnVtIFNFTEVDVCAnT2xlJywgY3VycmVudF9kYXRlIEZST00gZ2VuZXJhdGVf
c2VyaWVzKCAxLCAxMDAwICk7DQpJTlNFUlQgSU5UTyB0YWJsZV90ZXN0X2VudW0gU0VMRUNU
ICdCb2xlJywgY3VycmVudF9kYXRlIEZST00gZ2VuZXJhdGVfc2VyaWVzKCAxLCAxMDAwICk7
DQpJTlNFUlQgSU5UTyB0YWJsZV90ZXN0X2VudW0gU0VMRUNUICdTa29sZScsIGN1cnJlbnRf
ZGF0ZSBGUk9NIGdlbmVyYXRlX3NlcmllcyggMSwgMTAwMCApOw0KSU5TRVJUIElOVE8gdGFi
bGVfdGVzdF9lbnVtIFZBTFVFUyAoICdPbGUnLCBOVUxMICk7DQpWQUNVVU0gKGFuYWx5emUp
IHRhYmxlX3Rlc3RfZW51bTsNCg0KRVhQTEFJTiAoYW5hbHl6ZSwgY29zdHMsIGJ1ZmZlcnMs
IHZlcmJvc2UpIFNFTEVDVCB2YWwgRlJPTSB0YWJsZV90ZXN0X2VudW0gV0hFUkUgdmFsID0g
J09sZScgYW5kIGRhdCBJUyBOVUxMOw0KDQpQUkVQQVJFIHFyeTEodmFyY2hhcikgQVMgU0VM
RUNUIHZhbCBGUk9NIHRhYmxlX3Rlc3RfZW51bSBXSEVSRSB2YWwgPSAkMTo6dHlwZV90YWJs
ZV90ZXN0X2VudW0gQU5EIGRhdCBJUyBOVUxMOw0KUFJFUEFSRSBxcnkyKHR5cGVfdGFibGVf
dGVzdF9lbnVtKSBBUyBzZWxlY3QgdmFsIEZST00gdGFibGVfdGVzdF9lbnVtIFdIRVJFIHZh
bCA9ICQxIEFORCBkYXQgSVMgTlVMTDsNClBSRVBBUkUgcXJ5Myh1bmtub3duKSBBUyBTRUxF
Q1QgdmFsIEZST00gdGFibGVfdGVzdF9lbnVtIFdIRVJFIHZhbCA9ICQxIEFORCBkYXQgSVMg
TlVMTDsNCg0KRVhQTEFJTiAoYW5hbHl6ZSwgY29zdHMsIGJ1ZmZlcnMsIHZlcmJvc2UpIEVY
RUNVVEUgcXJ5MSgnT2xlJyk7DQpFWFBMQUlOIChhbmFseXplLCBjb3N0cywgYnVmZmVycywg
dmVyYm9zZSkgRVhFQ1VURSBxcnkyKCdPbGUnKTsNCkVYUExBSU4gKGFuYWx5emUsIGNvc3Rz
LCBidWZmZXJzLCB2ZXJib3NlKSBFWEVDVVRFIHFyeTMoJ09sZScpOw0KDQpERUFMTE9DQVRF
IHFyeTE7DQpERUFMTE9DQVRFIHFyeTI7DQpERUFMTE9DQVRFIHFyeTM7DQoNCkRST1AgVEFC
TEUgdGFibGVfdGVzdF9lbnVtOw0KRFJPUCBUWVBFIHR5cGVfdGFibGVfdGVzdF9lbnVtOw==
--------------D8B0B3455A30F70A23DC82FB
Content-Type: text/plain; charset=UTF-8;
 name="enumTest.log"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="enumTest.log"

ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB2
ZXJzaW9uDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0NCiBQb3N0Z3JlU1FMIDEzLjQgKERlYmlhbiAxMy40LTEucGdkZzEw
MCsxKSBvbiB4ODZfNjQtcGMtbGludXgtZ251LCBjb21waWxlZCBieSBnY2MgKERlYmlhbiA4
LjMuMC02KSA4LjMuMCwgNjQtYml0DQooMSByb3cpDQoNCkNSRUFURSBUWVBFDQpDUkVBVEUg
VEFCTEUNCkNSRUFURSBJTkRFWA0KSU5TRVJUIDAgMTAwMA0KSU5TRVJUIDAgMTAwMA0KSU5T
RVJUIDAgMTAwMA0KSU5TRVJUIDAgMQ0KVkFDVVVNDQogICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQ
TEFODQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIEluZGV4
IE9ubHkgU2NhbiB1c2luZyB0YWJsZV90ZXN0X2VudW1fdmFsX2lkeCBvbiBwdWJsaWMudGFi
bGVfdGVzdF9lbnVtICAoY29zdD0wLjEyLi40LjE0IHJvd3M9MSB3aWR0aD00KSAoYWN0dWFs
IHRpbWU9MC4wMTQuLjAuMDE2IHJvd3M9MSBsb29wcz0xKQ0KICAgT3V0cHV0OiB2YWwNCiAg
IEhlYXAgRmV0Y2hlczogMA0KIFBsYW5uaW5nIFRpbWU6IDAuNDM2IG1zDQogRXhlY3V0aW9u
IFRpbWU6IDAuMDQ4IG1zDQooNSByb3dzKQ0KDQpQUkVQQVJFDQpQUkVQQVJFDQpQUkVQQVJF
DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBR
VUVSWSBQTEFODQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLQ0KIFNlcSBTY2FuIG9uIHB1YmxpYy50YWJsZV90ZXN0X2VudW0g
IChjb3N0PTAuMDAuLjY2LjUyIHJvd3M9MSB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MS4xMzEu
LjEuMTMzIHJvd3M9MSBsb29wcz0xKQ0KICAgT3V0cHV0OiB2YWwNCiAgIEZpbHRlcjogKCh0
YWJsZV90ZXN0X2VudW0uZGF0IElTIE5VTEwpIEFORCAodGFibGVfdGVzdF9lbnVtLnZhbCA9
ICgnT2xlJzo6Y3N0cmluZyk6OnR5cGVfdGFibGVfdGVzdF9lbnVtKSkNCiAgIFJvd3MgUmVt
b3ZlZCBieSBGaWx0ZXI6IDMwMDANCiBQbGFubmluZyBUaW1lOiAwLjI2MSBtcw0KIEV4ZWN1
dGlvbiBUaW1lOiAxLjE2MiBtcw0KKDYgcm93cykNCg0KICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkg
UExBTg0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBJbmRl
eCBPbmx5IFNjYW4gdXNpbmcgdGFibGVfdGVzdF9lbnVtX3ZhbF9pZHggb24gcHVibGljLnRh
YmxlX3Rlc3RfZW51bSAgKGNvc3Q9MC4xMi4uNC4xNCByb3dzPTEgd2lkdGg9NCkgKGFjdHVh
bCB0aW1lPTAuMDIzLi4wLjAyNiByb3dzPTEgbG9vcHM9MSkNCiAgIE91dHB1dDogdmFsDQog
ICBIZWFwIEZldGNoZXM6IDANCiBQbGFubmluZyBUaW1lOiAwLjI1NiBtcw0KIEV4ZWN1dGlv
biBUaW1lOiAwLjA1NCBtcw0KKDUgcm93cykNCg0KICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkgUExB
Tg0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBJbmRleCBP
bmx5IFNjYW4gdXNpbmcgdGFibGVfdGVzdF9lbnVtX3ZhbF9pZHggb24gcHVibGljLnRhYmxl
X3Rlc3RfZW51bSAgKGNvc3Q9MC4xMi4uNC4xNCByb3dzPTEgd2lkdGg9NCkgKGFjdHVhbCB0
aW1lPTAuMDE5Li4wLjAyMyByb3dzPTEgbG9vcHM9MSkNCiAgIE91dHB1dDogdmFsDQogICBI
ZWFwIEZldGNoZXM6IDANCiBQbGFubmluZyBUaW1lOiAwLjI1OSBtcw0KIEV4ZWN1dGlvbiBU
aW1lOiAwLjA1MSBtcw0KKDUgcm93cykNCg0KREVBTExPQ0FURQ0KREVBTExPQ0FURQ0KREVB
TExPQ0FURQ0KRFJPUCBUQUJMRQ0KRFJPUCBUWVBFDQo=
--------------D8B0B3455A30F70A23DC82FB--