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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
