Re: Bitmap index scans use of filters on available columns
| От | Simon Riggs |
|---|---|
| Тема | Re: Bitmap index scans use of filters on available columns |
| Дата | |
| Msg-id | CANP8+jJTUQUbQ5gy56hLw3tw+8C9DUX_VYP9WZVwPu0qm5dQGQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Bitmap index scans use of filters on available columns (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Bitmap index scans use of filters on available columns
|
| Список | pgsql-hackers |
On 4 November 2015 at 16:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
explain select * from f where x=5 and y like '%abc%';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on f (cost=382.67..9314.72 rows=1 width=37)
-> Bitmap Index Scan on f_x_y_idx (cost=0.00..382.67 rows=10433 width=0)
--
Simon Riggs <simon@2ndquadrant.com> writes:
> On 4 November 2015 at 15:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We generate this 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.
You're missing my point: that is possible in an indexscan, but *not* in a
bitmap indexscan, because the index AM APIs are totally different in the
two cases. In a bitmap scan, nothing more than a TID bitmap is ever
returned out to anyplace that could execute arbitrary expressions.
Still misunderstanding each other... sorry about that
If a btree can Filter y like that on an IndexScan, then it can also apply that Filter on y when it is looking through rows before it adds them to the bitmap.
I completely understand that it cannot return the value (of y) via the bitmap.
We should be able to get a plan like this
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on f (cost=382.67..9314.72 rows=1 width=37)
-> Bitmap Index Scan on f_x_y_idx (cost=0.00..382.67 rows=10433 width=0)
Index Cond: (x = 5)
>> Filter: (y ~~ '%abc%'::text)
if the bitmap stays non-lossy.
I see that plannodes.h says
"In a BitmapIndexScan plan node, the targetlist and qual fields are not used and are always NIL. "but it doesn't say why.
Why can't the BitmapIndexScan execute arbitrary expressions? (or What did you mean by that phrase?)
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-hackers по дате отправления: