From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Marc Cousin <cousinmarc@gmail.com>
Subject: Re: Avoid full GIN index scan when possible
Date: Sat, 29 Jun 2019 16:27:26 +0200
Message-ID: <20190629142726.gljead3qhbywahbx@development> (raw)
In-Reply-To: <CAOBaU_aNfDqzCDaVHax6PoNuukTppQfsvSSOVno8ZUKGboEPMg@mail.gmail.com>
References: <4547.1561748599@sss.pgh.pa.us>
<20190628195401.frwhcga76rrytbc4@development>
<7773.1561752983@sss.pgh.pa.us>
<CAOBaU_bDF-7E5x0WVPmmtoc+0z7S3hmcXnnV+1ggy8CMGyZxgg@mail.gmail.com>
<ae1ecc00-2de6-ea77-1236-f754292ba828@postgrespro.ru>
<CAOBaU_YgU=WmUN22uGHEjUTymqEpM_aZi=ETUZd2nhEZabBc4Q@mail.gmail.com>
<20190629102514.ucfzglxu7ccjsbjr@development>
<CAOBaU_bYBaobNoNJPrPdJ0vVf=-jf0MaU2MCzG8_We0ttu4Z3g@mail.gmail.com>
<20190629131135.ilktnqtollvbfnml@development>
<CAOBaU_aNfDqzCDaVHax6PoNuukTppQfsvSSOVno8ZUKGboEPMg@mail.gmail.com>
On Sat, Jun 29, 2019 at 03:28:11PM +0200, Julien Rouhaud wrote:
>On Sat, Jun 29, 2019 at 3:11 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>>
>> On Sat, Jun 29, 2019 at 02:50:51PM +0200, Julien Rouhaud wrote:
>> >On Sat, Jun 29, 2019 at 12:25 PM Tomas Vondra
>> >> A related issue is that during costing is too late to modify cardinality
>> >> estimates, so the 'Bitmap Index Scan' will be expected to return fewer
>> >> rows than it actually returns (after ignoring the full-scan quals).
>> >> Ignoring redundant quals (the way btree does it at execution) does not
>> >> have such consequence, of course.
>> >>
>> >> Which may be an issue, because we essentially want to modify the list of
>> >> quals to minimize the cost of
>> >>
>> >> bitmap index scan + recheck during bitmap heap scan
>> >>
>> >> OTOH it's not a huge issue, because it won't affect the rest of the plan
>> >> (because that uses the bitmap heap scan estimates, and those are not
>> >> affected by this).
>> >
>> >Doesn't this problem already exists, as the quals that we could drop
>> >can't actually reduce the node's results?
>>
>> How could it not reduce the node's results, if you ignore some quals
>> that are not redundant? My understanding is we have a plan like this:
>>
>> Bitmap Heap Scan
>> -> Bitmap Index Scan
>>
>> and by ignoring some quals at the index scan level, we trade the (high)
>> cost of evaluating the qual there for a plain recheck at the bitmap heap
>> scan. But it means the index scan may produce more rows, so it's only a
>> win if the "extra rechecks" are cheaper than the (removed) full scan.
>
>Sorry, by node I meant the BitmapIndexScan. AIUI, if you have for
>instance WHERE val LIKE '%abcde%' AND val LIKE '%z%' and a trgm index,
>the BitmapIndexScan will have to through the whole index and discard
>rows based on the only opclass-optimizable qual (LIKE '%abcde%'),
>letting the recheck do the proper filtering for the other qual. So
>whether you have the LIKE '%z%' or not in the index scan, the
>BitmapIndexScan will return the same number of rows, the only
>difference being that in one case you'll have to scan the whole index,
>while in the other case you won't.
>
Oh! I thought 'full scan' means we have to scan all the keys in the GIN
index, but we can still eliminate some of the keys (for example for the
trigrams we might check if the trigram contains the short string). But
clearly I was mistaken and it does not work like that ...
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, rjuju123@gmail.com, n.gluhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org, tgl@sss.pgh.pa.us, cousinmarc@gmail.com
Subject: Re: Avoid full GIN index scan when possible
In-Reply-To: <20190629142726.gljead3qhbywahbx@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox