Re: Using bitmap index scans-more efficient

Tom Lane wrote:

>Kyle Bateman <kyle(at)actarg(dot)com> writes:
>
>
>>But I'm assuming that using an interval-encoded project tree, I would
>>have to do something like the following to get a progency group:
>>
>>
>>select * from ledger l, proj p where p.proj_id = l.proj and p.left >
>>1234 and p.right < 2345;
>>
>>

Here's an interesting result:

I created a function proj_left(int4) that returns the left interval
number for a given project. Then I created an index on the underlying
table for the ledger view(which took forever to build) like so:

create index i_test on apinv_items (proj_left(proj));

Now my query:

select * from ledger where proj_left(dr_proj) >= 5283 and
proj_left(dr_proj) < 5300;

is very speedy. Problem is, I had to mark the function proj_left() as
immutable, which it can not be since the left and right values for a
given project will change any time a project is added, removed, or moved
around the hierarchy :(

So is there any good way to tell the planner to do several individual
index scans for the projects involved in the desired progeny, or the
results together and return the result? This is what it seems to be
choosing in the case of the query:

select * from ledger where proj in (4737,4789,4892,4893,4894,4895,4933,4934,4935);