Message-ID: <44E11E1A.8020507@actarg.com>
Date: Mon, 14 Aug 2006 19:06:34 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Florian Weimer <fweimer@bfk.de>
Subject: Re: Using bitmap index scans-more efficient
References: <44DF6582.4000806@actarg.com> <82mza77rch.fsf@mid.bfk.de>
In-Reply-To: <82mza77rch.fsf@mid.bfk.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Florian Weimer wrote:

>* Kyle Bateman:
>
>  
>
>>Any ideas about whether/how this can be done?
>>    
>>
>
>If the project tree is fairly consistent, it's convenient to encode it
>using intervals instead of parent/child intervals.  IIRC, Celko's "SQL
>for smarties" explains how to do this, and Kristian Koehntopp has
>written some PHP code to implement it.
>
>  
>
I agree that this produces a more efficient query for finding the 
projects that are the progeny of another project, but I'm trying to 
figure out how that helps me select the right project transactions from 
my ledger efficiently.

This query produces wonderful results (very fast):

select * from ledger where proj >= 4737 and proj <= 4740;

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;

The problem (at least according to my initial testing) is that this 
forces a join of the entire ledger and I get my slowest performance 
group (5 seconds).

What am I missing?

Kyle