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

Hello

2011/3/14 Jo <jl.news@uni-bonn.de>:
> 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.
>

these outputs are not too helpful - send a EXPLAIN ANALYZE result

you can use a small aplication for storing plans http://explain.depesz.com/

regards

Pavel Stehule

>
> *************************************
> 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
>>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>