Re: Avoid full GIN index scan when possible

On Sat, Jun 29, 2019 at 3:51 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> On Sat, Jun 29, 2019 at 12:25 PM Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
> >
> > On Sat, Jun 29, 2019 at 11:10:03AM +0200, Julien Rouhaud wrote:
> > >On Sat, Jun 29, 2019 at 12:51 AM Nikita Glukhov
> > >> -- patched
> > >> EXPLAIN ANALYZE SELECT * FROM test WHERE t LIKE '%1234%' AND t LIKE '%1%';
> > >>                                                       QUERY PLAN
> > >> -----------------------------------------------------------------------------------------------------------------------
> > >>  Bitmap Heap Scan on test  (cost=20.43..176.79 rows=42 width=6) (actual time=0.287..0.424 rows=300 loops=1)
> > >>    Recheck Cond: ((t ~~ '%1234%'::text) AND (t ~~ '%1%'::text))
> > >>    Rows Removed by Index Recheck: 2
> > >>    Heap Blocks: exact=114
> > >>    ->  Bitmap Index Scan on test_t_idx  (cost=0.00..20.42 rows=42 width=0) (actual time=0.271..0.271 rows=302 loops=1)
> > >>          Index Cond: ((t ~~ '%1234%'::text) AND (t ~~ '%1%'::text))
> > >>  Planning Time: 0.080 ms
> > >>  Execution Time: 0.450 ms
> > >> (8 rows)
> > >
> > >One thing that's bothering me is that the explain implies that the
> > >LIKE '%i% was part of the index scan, while in reality it wasn't.  One
> > >of the reason why I tried to modify the qual while generating the path
> > >was to have the explain be clearer about what is really done.
> >
> > Yeah, I think that's a bit annoying - it'd be nice to make it clear
> > which quals were actually used to scan the index. It some cases it may
> > not be possible (e.g. in cases when the decision is done at runtime, not
> > while planning the query), but it'd be nice to show it when possible.
>
> Maybe we could somehow add some runtime information about ignored
> quals, similar to the "never executed" information for loops?

+1,
This sounds reasonable for me.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company