Re: Avoid full GIN index scan when possible

On Sat, Jun 29, 2019 at 3:11 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)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.

> clearly whatever we do the results from the bitmap heap scan
> must remain the same.

Of course.