Re: Postgres 8.3 vs. 8.4 - Query plans and performance - Mailing list pgsql-general

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
>