Message-ID: <44E25599.4060905@actarg.com>
Date: Tue, 15 Aug 2006 17:15:37 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-sql@postgresql.org, Florian Weimer <fweimer@bfk.de>
Subject: Re: Using bitmap index scans-more efficient
References: <44DF6582.4000806@actarg.com> <82mza77rch.fsf@mid.bfk.de>
	<44E11E1A.8020507@actarg.com> <27367.1155606817@sss.pgh.pa.us>
In-Reply-To: <27367.1155606817@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

>Kyle Bateman <kyle@actarg.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);