Hello 2011/3/14 Jo <jl.news@xxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
