From: | <Masahiro(dot)Ikeda(at)nttdata(dot)com> |
---|---|
To: | <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Cc: | <Masao(dot)Fujii(at)nttdata(dot)com> |
Subject: | Is it expected behavior index only scan shows "OPERATOR(pg_catalog." for EXPLAIN? |
Date: | 2024-07-08 11:03:51 |
Message-ID: | TYWPR01MB10982EA60FDC58E3BCCB54A1BB1DA2@TYWPR01MB10982.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
While I'm researching about [1], I found there are inconsistent EXPLAIN outputs.
Here is an example which shows " OPERATOR(pg_catalog.". Though it's not wrong,
I feel like there is no consistency in the output format.
-- A reproduce procedure
create temp table btree_bpchar (f1 text collate "C");
create index on btree_bpchar(f1 bpchar_ops);
insert into btree_bpchar values ('foo'), ('fool'), ('bar'), ('quux');
set enable_seqscan to false;
set enable_bitmapscan to false;
set enable_indexonlyscan to false; -- or true
explain (costs off)
select * from btree_bpchar where f1::bpchar like 'foo';
-- Index Scan result
QUERY PLAN
------------------------------------------------------
Index Scan using btree_bpchar_f1_idx on btree_bpchar
Index Cond: ((f1)::bpchar = 'foo'::bpchar)
Filter: ((f1)::bpchar ~~ 'foo'::text)
(3 rows)
-- Index Only Scan result which has 'OPERATOR'
QUERY PLAN
-----------------------------------------------------------
Index Only Scan using btree_bpchar_f1_idx on btree_bpchar
Index Cond: (f1 OPERATOR(pg_catalog.=) 'foo'::bpchar) -- Here is the point.
Filter: ((f1)::bpchar ~~ 'foo'::text)
(3 rows)
IIUC, the index only scan use fixed_indexquals, which is removed "RelabelType" nodes,
for EXPLAIN so that get_rule_expr() could not understand the left argument of the operator
(f1 if the above case) can be displayed with arg::resulttype and it doesn't need to
show "OPERATOR(pg_catalog.)".
I've attached PoC patch to show a simple solution. It just adds a new member "indexqualorig"
to the index only scan node like the index scan and the bitmap index scan. But, since I'm
a beginner about the planner, I might have misunderstood something or there should be better
ways.
BTW, I'm trying to add a new index AM interface for EXPLAIN on the thread([1]). As the aspect,
my above solution might not be ideal because AMs can only know index column ids (varattno)
from fixed_indexquals. In that case, to support "fixed_indexquals" as argument of deparse_expression()
is better.
[1] Improve EXPLAIN output for multicolumn B-Tree Index
https://www.postgresql.org/message-id/flat/TYWPR01MB1098260B694D27758FE2BA46FB1C92%40TYWPR01MB10982.jpnprd01.prod.outlook.com
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Fix-inconsistent-explain-output-for-index-only-sc.patch | application/octet-stream | 9.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2024-07-08 11:32:31 | Re: Is it expected behavior index only scan shows "OPERATOR(pg_catalog." for EXPLAIN? |
Previous Message | Matthias van de Meent | 2024-07-08 10:45:13 | Re: Thoughts on NBASE=100000000 |