Date: Sat, 22 Jun 2024 00:31:09 +0900
From: Yugo NAGATA <nagata@sraoss.co.jp>
To: <Masahiro.Ikeda@nttdata.com>
Cc: <pgsql-hackers@lists.postgresql.org>, <Masao.Fujii@nttdata.com>
Subject: Re: Improve EXPLAIN output for multicolumn B-Tree Index
Message-Id: <20240622003109.6a4db3d72cd296cc3a89c4c9@sraoss.co.jp>
In-Reply-To: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
References: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20240622003109.6a4db3d72cd296cc3a89c4c9%40sraoss.co.jp>
Precedence: bulk

On Fri, 21 Jun 2024 07:12:25 +0000
<Masahiro.Ikeda@nttdata.com> wrote:

> * Is this feature useful? Is there a possibility it will be accepted?

I think adding such information to EXPLAIN outputs is useful because it
will help users confirm the effect of a multicolumn index on a certain query
and decide to whether leave, drop, or recreate the index, and so on.
 
> * Are there any other ideas for determining if multicolumn indexes are
> 
> being used efficiently? Although I considered calculating the efficiency using
> 
> pg_statio_all_indexes.idx_blks_read and pg_stat_all_indexes.idx_tup_read,
> 
>  I believe improving the EXPLAIN output is better because it can be output
> 
> per query and it's more user-friendly.

It seems for me improving EXPLAIN is a natural way to show information
on query optimization like index scans.
 
> * Is "Index Bound Cond" the proper term?I also considered changing
> 
> "Index Cond" to only show quals for the boundary condition and adding
> 
> a new term "Index Filter".

"Index Bound Cond" seems not intuitive for me because I could not find
description explaining what this means from the documentation. I like
"Index Filter" that implies the index has to be scanned.
 
> * Would it be better to add new interfaces to Index AM? Is there any case
> 
>   to output the EXPLAIN for each index context? At least, I think it's worth
> 
>   considering whether it's good for amcostestimate() to modify the
> 
>   IndexPath directly as the PoC patch does.

I am not sure it is the best way to modify IndexPath in amcostestimate(), but
I don't have better ideas for now.

Regards,
Yugo Nagata

> 
> 
> 
> 
> Regards,
> 
> --
> 
> Masahiro Ikeda
> 
> NTT DATA CORPORATION
> 
> 


-- 
Yugo NAGATA <nagata@sraoss.co.jp>