public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected]
To: [email protected]
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
Date: Wed, 26 Jun 2024 07:44:47 +0000
Message-ID: <TYWPR01MB10982D8D6B11B8E3F4ADDCEC2B1D62@TYWPR01MB10982.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAEze2WgMUz6nx+xanJnhq1PGDHBOeLMv8A4NNeWoBqsLHOwwQA@mail.gmail.com>
References: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
<CAExHW5sdpKOO+cj9=UBx_vSv=0mt2ncjJyrpdwgmbxFmWY0THg@mail.gmail.com>
<TYWPR01MB109825DAFBCA3233CA7EE77D7B1D42@TYWPR01MB10982.jpnprd01.prod.outlook.com>
<CAEze2Wj1SS8zFXS4YOWuMRmU5FQRM4N0vi_L4A3LPANoAeKL8w@mail.gmail.com>
<CAGECzQTuH_MEApdWujbZh40SoNr4sB5J-kM8jJ+KtZBARLCE8g@mail.gmail.com>
<CAEze2WgooitYVHO7tdVnewLM=hu8t4Eah2WYyAs25t5KCf7-oQ@mail.gmail.com>
<CAGECzQQ+x2oUupuY4_2MfppptK39MTfbKnWsjkN_38aJbF7zgw@mail.gmail.com>
<CAEze2WgMUz6nx+xanJnhq1PGDHBOeLMv8A4NNeWoBqsLHOwwQA@mail.gmail.com>
> On Mon, 24 Jun 2024 at 14:42, Jelte Fennema-Nio <[email protected]> wrote:
> >
> > On Mon, 24 Jun 2024 at 13:02, Matthias van de Meent
> > <[email protected]> wrote:
> > > It does not really behave similar: index scan keys (such as the
> > > id3=101 scankey) don't require visibility checks in the btree code,
> > > while the Filter condition _does_ require a visibility check, and
> > > delegates the check to the table AM if the scan isn't Index-Only, or
> > > if the VM didn't show all-visible during the check.
> >
> > Any chance you could point me in the right direction for the
> > code/docs/comment about this? I'd like to learn a bit more about why
> > that is the case, because I didn't realize visibility checks worked
> > differently for index scan keys and Filter keys.
>
> This can be derived by combining how Filter works (it only filters the returned live tuples)
> and how Index-Only scans work (return the index tuple, unless !ALL_VISIBLE, in which
> case the heap tuple is projected). There have been several threads more or less
> recently that also touch this topic and closely related topics, e.g. [0][1].
Thanks! I could understand what is difference between INCLUDE based filter and index filter.
> > > As you can see, there's a huge difference in performance. Putting
> > > both non-bound and "normal" filter clauses in the same Filter clause
> > > will make it more difficult to explain performance issues based on
> > > only the explain output.
> >
> > Fair enough, that's of course the main point of this patch in the
> > first place: being able to better interpret the explain plan when you
> > don't have access to the schema. Still I think Filter is the correct
> > keyword for both, so how about we make it less confusing by making the
> > current "Filter" more specific by calling it something like "Non-key
> > Filter" or "INCLUDE Filter" and then call the other something like
> > "Index Filter" or "Secondary Bound Filter".
>
> I'm not sure how debuggable explain plans are without access to the schema, especially
> when VERBOSE isn't configured, so I would be hesitant to accept that as an argument
> here.
IMHO, it's nice to be able to understand the differences between each
FILTER even without the VERBOSE option. (+1 for Jelte Fennema-Nio's idea)
Even without access to the schema, it would be possible to quickly know if
the plan is not as expected, and I believe there are virtually no disadvantages
to having multiple "XXX FILTER" outputs.
If it's better to output such information only with the VERBOSE option,
What do you think about the following idea?
* When the VERBOSE option is not specified, output as "Filter" in all cases
* When the VERBOSE option is specified, output as "Non-key Filter", "INCLUDE Filter"
and "Index Filter".
In addition, I think it would be good to mention the differences between each filter in
the documentation.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
view thread (11+ 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], [email protected], [email protected]
Subject: RE: Improve EXPLAIN output for multicolumn B-Tree Index
In-Reply-To: <TYWPR01MB10982D8D6B11B8E3F4ADDCEC2B1D62@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