From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>, Antonin Houska <ah(at)cybertec(dot)at>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Bitmap index scans use of filters on available columns |
Date: | 2015-11-04 18:25:29 |
Message-ID: | CAMkU=1zyDnfeOZ0jLg5+-fLpk2m1+YB++9yFbceMvkijkiEd8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Nov 4, 2015 at 7:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> On 4 November 2015 at 15:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
I had thought it must already be able to execute arbitrary
expressions, due to the ability to already support user-defined btree
ops (and ops of non-btree types in the case of other index types).
Does that only require a restricted execution environment?
> In the case at hand, the planner should have considered a plan of this
> shape as well. Presumably it concluded it was more expensive than using
> the bitmap approach. Jeff might try "set enable_bitmapscan = 0" and
> compare the estimated and actual costs.
It is a simplified test case, I can get it to do about anything I want
by tweaking the size, selectivity, columns selected, and cache warmth.
My understanding is that the normal (not index-only) index scan, which
is thought to be slower and actually is slower with a hot cache, also
applies the filter on the heap tuple, not the index tuple. But since
it only matters much for large queries and large queries tend to
prefer bitmaps, I was less interested in that case. And bitmaps can
feed into BitmapAnd, which is very helpful in avoiding index-creep
where every query gets a new index tailored for it. I think we are
missing a trick here that could apply to lots of situations.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2015-11-04 18:29:13 | Re: [patch] Proposal for \rotate in psql |
Previous Message | Robert Haas | 2015-11-04 18:13:58 | Re: Bitmap index scans use of filters on available columns |