Subject: Re: Improve EXPLAIN output for multicolumn B-Tree Index

From: Yugo NAGATA <[email-masked]>
Date: Sat, 22 Jun 2024 00:31:09 +0900

On Fri, 21 Jun 2024 07:12:25 +0000
<[email-masked]> 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 <[email-masked]>

---

From: <[email-masked]>
Date: Mon, 24 Jun 2024 02:52:10 +0000

> 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.

Thank you for your comments and for empathizing with the utility of the approach.

> It seems for me improving EXPLAIN is a natural way to show information on query
> optimization like index scans.

OK, I'll proceed with the way.

> "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.

OK, I think you are right. Even at this point, there are things like ‘Filter’ and
‘Rows Removed by Filter’, so it seems natural to align with them. I described a
new output example in the previous email, how about that?

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

OK, I’ll consider what the best way to change is. In addition, if we add
"Rows Removed by Index Filter", we might need to consider a method to receive the
number of filtered tuples at execution time from Index AM.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION