From: "Leif B. Kristensen" <leif@solumslekt.org>
To: "Pgsql-sql@postgresql.org" <Pgsql-sql@postgresql.org>
Subject: Re: Use of partial index
User-Agent: KMail/1.8.1
References: <F18A6F7CF1661F46920F2CF713122FED46CCC0@mail.aveo.aveopharma.com>
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CCC0@mail.aveo.aveopharma.com>
MIME-Version: 1.0
Content-Disposition: inline
Date: Wed, 5 Oct 2005 19:21:59 +0200
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200510051921.59324.leif@solumslekt.org>

On Wednesday 05 October 2005 18:44, you wrote:
> As I understand it, partial indices are generally useful when you
> only want to index a range of values, or if the select condition is
> on a different field from the one being indexed (eg: ON foo (a) WHERE
> b IS NOT NULL).
>
> I am just guessing here, but it sounds like 'person_fk = 2' is going
> to be a lot more selective (ie return less rows) than 'tag_type_fk =
> 2', so it's quicker to use the pkey and then filter the results.

That makes a lot of sense to me. As any person will usually participate 
in several events, I'll estimate that the ratio between person_fk=x and 
tag_type_fk=y is about 1:4.

> Depending on how many 'tag_type' values you have, indexing on it will
> not help at all.  In other words, if more than a few percent of the
> rows have the value '2' for 'tag_type_fg', postgres will tend to
> favour more selective indices if you are doing a join, or a seqscan
> if you are doing a straight select on that value.

I have only 53 different tag types, but most of them are rather rare. 
The most heavilyly used are birth=2, death=3, and marriage=4, and I've 
created similar partial indexes for the other two. In a previous MySQL 
project that's roughly sharing the same data model, I created a 
redundant table called "marriages" that speeded up the generation of a 
family view by a factor of four. That's why I thought that a partial 
index would have a similar effect here.

> Does that help?

Yessir, thank you very much!

> Dmitri
> PS Your query seems to be quite quick already, why don't you like
> this plan?

I didn't say that I didn't like it, - I just was a little perplexed :-)
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE