Is it expected behavior index only scan shows "OPERATOR(pg_catalog." for EXPLAIN?

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

Responses

Browse pgsql-hackers by date

  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