From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Cc: Florian Weimer <fweimer@bfk.de>
Subject: Re: Using bitmap index scans-more efficient
Date: Mon, 14 Aug 2006 19:06:34 -0600
Message-ID: <44E11E1A.8020507@actarg.com> (raw)
In-Reply-To: <82mza77rch.fsf@mid.bfk.de>
References: <44DF6582.4000806@actarg.com>
<82mza77rch.fsf@mid.bfk.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com, fweimer@bfk.de
Subject: Re: Using bitmap index scans-more efficient
In-Reply-To: <44E11E1A.8020507@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox