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

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"
" -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=451600
width=8)"
" Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
" SubPlan 3"
" -> Limit (cost=0.00..8.97 rows=1 width=14)"
" -> Index Scan using idx_relation_tags_relation_id on
relation_tags (cost=0.00..8.97 rows=1 width=14)"
" Index Cond: (relation_id = $0)"
" Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
" SubPlan 4"
" -> 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:de'::text) AND (v !~~* ''::text))"
" SubPlan 5"
" -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=52 width=8)"
" Filter: ((k ~~* 'name:de'::text) AND (v !~~* ''::text))"

*************************************
The beginning of the 8.3 query plan:
*************************************
"Seq Scan on relations (cost=0.00..491771409147.20 rows=334529 width=24)"
" Filter: ((subplan) AND ((subplan) OR (subplan)))"
" SubPlan"
" -> Seq Scan on relation_tags (cost=0.00..138595.94 rows=170556
width=0)"
" Filter: ((k ~~* 'boundary'::text) OR ((k ~~* 'type'::text)
AND (v ~~* 'boundary'::text) AND (relation_id = $0)))"
" -> Nested Loop (cost=3960.20..112931.40 rows=1 width=0)"
" -> HashAggregate (cost=3960.20..3960.69 rows=49 width=8)"
" -> Index Scan using idx_relation_members_relation_id
on relation_members (cost=0.00..3959.83 rows=148 width=8)"
" Index Cond: (relation_id = $0)"
" Filter: ((member_role ~~* 'outer'::text) AND
(member_type = 'W'::bpchar))"
" -> Index Scan using idx_way_tags_way_id on way_tags
(cost=0.00..2223.88 rows=1 width=8)"
" Index Cond: (public.way_tags.way_id =
public.relation_members.member_id)"
" Filter: ((public.way_tags.k ~~* 'boundary'::text) OR
((public.way_tags.k ~~* 'type'::text) AND (public.way_tags.v ~~*
'boundary'::text)))"
" -> GroupAggregate (cost=0.00..29281.72 rows=1 width=8)"
" Filter: (count(*) = 1)"
" -> Index Scan using idx_relation_members_relation_id on
relation_members (cost=0.00..29281.33 rows=74 width=8)"
" Index Cond: (relation_id = $0)"
" Filter: ((member_role = 'outer'::text) AND (member_type
= 'W'::bpchar) AND (subplan))"
" SubPlan"
" -> Index Scan using idx_ways_id on ways
(cost=0.00..22.75 rows=1 width=0)"
" Index Cond: (id = $2)"
" Filter: (st_isclosed(linestring) AND
(st_numpoints(linestring) > 3))"
" -> Nested Loop (cost=3957.48..5255.55 rows=6 width=978)"
" -> HashAggregate (cost=3957.48..3958.05 rows=57 width=8)"
" -> Index Scan using idx_relation_members_relation_id
on relation_members (cost=0.00..3957.04 rows=173 width=8)"
" Index Cond: (relation_id = $0)"
" Filter: (member_role ~~* 'outer'::text)"

Jo

On 14.03.2011 14:15, Hannes Erven wrote:
> Jo,
>
>
>> we have performance problems running several queries pon postgres 8.4 .
>> Using the previous version (8.3) our queries performs well
>> (The queries are quite complex, consisting of several sub-queries and
>> various spatial functions).
>>
>> Are there some major changes from 8.3 to 8.4 that cause this bad
>> performance?
>
> Have you checked the release notes?
> http://www.postgresql.org/docs/8.4/static/release-8-4.html
>
> If you can, I'd also suggest that you show the execution plans on
> 8.3/8.4 on the list.
>
> One more thing: did you also check the server configuration (work_mem, ...)?
>
>
> -hannes
>