On Mon, Mar 14, 2011 at 9:48 AM, Jo <jl.news@uni-bonn.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
