From: Kyle Bateman <kyle@actarg.com>
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
Date: Tue, 15 Aug 2006 17:15:37 -0600
Message-ID: <44E25599.4060905@actarg.com> (raw)
In-Reply-To: <27367.1155606817@sss.pgh.pa.us>
References: <44DF6582.4000806@actarg.com>
<82mza77rch.fsf@mid.bfk.de>
<44E11E1A.8020507@actarg.com>
<27367.1155606817@sss.pgh.pa.us>
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);
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, tgl@sss.pgh.pa.us, fweimer@bfk.de
Subject: Re: Using bitmap index scans-more efficient
In-Reply-To: <44E25599.4060905@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