RE: Improve EXPLAIN output for multicolumn B-Tree Index

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

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

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

OK, I'll proceed with the way.

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

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?

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

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