From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Jo <jl(dot)news(at)uni-bonn(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres 8.3 vs. 8.4 - Query plans and performance |
Date: | 2011-03-14 14:52:36 |
Message-ID: | AANLkTinVGpGUS94hapnLWeghmcB6uqGCdVtCh6g8csBb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
2011/3/14 Jo <jl(dot)news(at)uni-bonn(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Pyhalov | 2011-03-14 14:54:26 | Re: DBMS upgrade and backups |
Previous Message | Jo | 2011-03-14 14:48:03 | Re: Postgres 8.3 vs. 8.4 - Query plans and performance |