From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>, Antonin Houska <ah(at)cybertec(dot)at>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Subject: | Re: Bitmap index scans use of filters on available columns |
Date: | 2015-11-04 15:07:57 |
Message-ID: | CANP8+jJETjv+pVhFJYJNr2O8DCsvbLrSxdCRDjtW94AmoOv6aw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 4 November 2015 at 15:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> writes:
> > 2015-11-04 Antonin Houska <ah(at)cybertec(dot)at>:
> >> (see expand_indexqual_opclause()), I'm not sure any kind of expansion is
> >> possible for '%abc%' which would result in a b-tree searchable
> condition.
>
> > I think the question is not about using the b-tree for checking the
> > condition, but about just retrieving the value for y from the index,
> > and just using that to check the condition before fetching the
> > corresponding tuple from the heap.
>
> Bitmap index scans only return TID bitmaps, not index tuples; indeed
> the underlying index may not store anything recognizable as tuples.
>
Agreed, though the OP's question was asking why a Filter condition can't be
added to a BitmapIndexScan, so that non-qualifying rows can be excluded
from the TID bitmap it generates.
We generate this plan
postgres=# explain select * from f where x=5 and y like '%abc%';
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using f_x_y_idx on f (cost=0.42..26075.71 rows=209 width=37)
Index Cond: (x = 5)
Filter: (y ~~ '%abc%'::text)
So it should be possible to do the Filter condition on the BitmapIndexScan.
--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2015-11-04 15:11:44 | Re: extend pgbench expressions with functions |
Previous Message | Pavel Stehule | 2015-11-04 14:54:59 | Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions |