Re: Postgres 8.3 vs. 8.4 - Query plans and performance

> On 15.03.2011 17:24, Merlin Moncure wrote:
>>
>>
>> well, regardless of the version, you're doing a gazillion sequential
>> scans on relation tags. This looks like the primary culprit (I had to
>> look up the ~~* operator...it's 'ilike'):
>> (
>> (k ~~* 'boundary'::text) OR
>> (
>> (k ~~* 'type'::text)
>> AND (v ~~* 'boundary'::text)
>> AND (relation_id = $0)
>> )
>> )
>>
>> 1. do we really. really need to be using ~~* here? how about '~~' (like)
>> or '='
>> 2. can we see definition and indexes on relation_tags? In particular,
>> have you considered an index on (k,v,relation_id), or maybe one on
>> (relation_id, v, k) and one on k?
>> 3. can we see the source query?

your index can't be used to it's full effect because you are doing
case insensitive matching. if you read the plan, the index is only
matching on relation_id I'm extremely skeptical you really need to
be matching this way -- either:

1. change ilike to like
2. change to ilike like, but add lower(), or upper() around matching
terms as needed: k like lower('boundary') etc
3. adjust your index like this:
create index on relation_tags(relation_id, lower(v), lower(k))
and your matching to this:
lower(k) like lower('type'), lower(v) like lower('boundary'), etc

1 is simplest and preferred if it meets your requirements, 2 next simplest, etc

also an index on just k will probably help. OR, you might see great
benefit from reversing the terms, so your index is on k,v,relation_id.
but fix the insensitive issue first.

It's hard to say for sure, because we don't have a full explain
analyze for either version so we can't know for sure what's going
wrong. 8.4 is generating what it thinks is a better plan, and it's
difficult to see where the problems lies without being able to see
mis-estimates which are often a major contributor to bad plans. that
said: with small changes your query can likely be made to go
*significantly* faster.

I'd also like to see the query.

merlin