RE: Improve EXPLAIN output for multicolumn B-Tree Index

From: <Masahiro(dot)Ikeda(at)nttdata(dot)com>
To: <boekewurm+postgres(at)gmail(dot)com>
Cc: <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, <pgsql-hackers(at)lists(dot)postgresql(dot)org>, <Masao(dot)Fujii(at)nttdata(dot)com>
Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
Date: 2024-06-26 06:45:38
Message-ID: TYWPR01MB10982B0088F478C2269EAAF37B1D62@TYWPR01MB10982.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> On Mon, 24 Jun 2024 at 04:38, <Masahiro(dot)Ikeda(at)nttdata(dot)com> wrote:
> >
> > In my local PoC patch, I have modified the output as follows, what do you think?
> >
> > =# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id2 =
> 101;
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > ---------------------------------------------------
> > Index Scan using test_idx on ikedamsh.test (cost=0.42..8.45 rows=1 width=18)
> (actual time=0.082..0.086 rows=1 loops=1)
> > Output: id1, id2, id3, value
> > Index Cond: ((test.id1 = 1) AND (test.id2 = 101)) -- If it’s efficient, the output
> won’t change.
> > Planning Time: 5.088 ms
> > Execution Time: 0.162 ms
> > (5 rows)
> >
> > =# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id3 =
> 101;
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > ---------------------------------------------------------
> > Index Scan using test_idx on ikedamsh.test (cost=0.42..12630.10 rows=1
> width=18) (actual time=0.175..279.819 rows=1 loops=1)
> > Output: id1, id2, id3, value
> > Index Cond: (test.id1 = 1) -- Change the output. Show only the
> bound quals.
> > Index Filter: (test.id3 = 101) -- New. Output quals which are not
> used as the bound quals
>
> I think this is too easy to confuse with the pre-existing 'Filter'
> condition, which you'll find on indexes with INCLUDE-d columns or filters on non-index
> columns.

Thanks for your comment. I forgot the case.

> Furthermore, I think this is probably not helpful (maybe even harmful) for index types
> like GIN and BRIN, where index searchkey order is mostly irrelevant to the index shape
> and performance.

Yes, I expected that only B-Tree index support the feature.

> Finally, does this change the index AM API? Does this add another scankey argument to
> ->amrescan?

Yes, I think so. But since I'd like to make users know the index scan will happen without
ANALYZE, I planned to change amcostestimate for "Index Filter" and amrescan() for
"Rows Removed by Index Filter".

> > Rows Removed by Index Filter: 499999 -- New. Output when ANALYZE option
> is specified
>
> Separate from the changes to Index Cond/Index Filter output changes I think this can
> be useful output, though I'd probably let the AM specify what kind of filter data to
> display.
> E.g. BRIN may well want to display how many ranges matched the predicate, vs how
> many ranges were unsummarized and thus returned; two conditions which aren't as
> easy to differentiate but can be important debugging query performance.

OK, thanks. I understood that it would be nice if we could customize to output information
specific to other indexes like BRIN.

> > Planning Time: 0.354 ms
> > Execution Time: 279.908 ms
> > (7 rows)
>
> Was this a test against the same dataset as the one you'd posted your measurements of
> your first patchset with? The execution time seems to have slown down quite
> significantly, so if the testset is the same then this doesn't bode well for your patchset.

Yes, the reason is that the cache hit ratio is very low since I tested after I restarted the
machine. I had to add BUFFERS option.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Anthonin Bonnefoy 2024-06-26 06:48:06 Re: Fix possible overflow of pg_stat DSA's refcnt
Previous Message Bertrand Drouvot 2024-06-26 06:42:30 Re: New standby_slot_names GUC in PG 17