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

On Mon, Mar 14, 2011 at 9:48 AM, Jo <jl(dot)news(at)uni-bonn(dot)de> wrote:
> I set the work_mem to 100MB and the shared buffers are 2 GB
>
> The query plans are long and complex. I send the beginning of the
> two plans. Hope this helps to understand the differences.
> I assume the join strategy in 8.3 differs from the one in 8.4.
>
>
> *************************************
> The beginning of the 8.4:
> *************************************
> "Seq Scan on relations  (cost=0.00..1502557856.52 rows=332613 width=24)"
> "  Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))"
> "  SubPlan 1"
> "    ->  Index Scan using idx_relation_tags_relation_id on relation_tags
>  (cost=0.00..8.97 rows=1 width=0)"
> "          Index Cond: (relation_id = $0)"
> "          Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
> "  SubPlan 2"

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?

merlin