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