public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
Date: Mon, 24 Jun 2024 02:52:10 +0000
Message-ID: <TYWPR01MB109823CB6D43D728D2255D54DB1D42@TYWPR01MB10982.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
	<[email protected]>

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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
  In-Reply-To: <TYWPR01MB109823CB6D43D728D2255D54DB1D42@TYWPR01MB10982.jpnprd01.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox