Hi Thom,
I did a select count(*) from xdf.xdf_admin_hierarchy and it returns 84211 on
both databases postgres 8.3.8 and 8.4.1.
The amount of data is exactly the same in both databases as they are restored
from the same dump.
Regards
David
_____
Von: Thom Brown [mailto:[email protected]]
Gesendet: Dienstag, 8. Dezember 2009 11:12
An: Schmitz, David
Cc: Andres Freund; [email protected]
Betreff: Re: [PERFORM] performance penalty between Postgresql 8.3.8 and
8.4.1
2009/12/8 Schmitz, David <[email protected]>
Hi Andres,
EXPLAIN ANALYZE
select ah.ORDER8_ID, ah.BUILTUP_ID, rl.LINK_ID, la.SIDE,
rl.ROAD_NAME_ID, rl.LEFT_ADDRESS_RANGE_ID,
rl.RIGHT_ADDRESS_RANGE_ID,
rl.IS_EXIT_NAME, rl.EXPLICATABLE,
rl.IS_JUNCTION_NAME,
rl.IS_NAME_ON_ROADSIGN, rl.IS_POSTAL_NAME,
rl.IS_STALE_NAME,
rl.IS_VANITY_NAME, rl.ROAD_LINK_ID,
rn.STREET_NAME,
rn.ROUTE_TYPE
from rdf.xdf_ADMIN_HIERARCHY ah
join xdf.xdf_LINK_ADMIN la
on ah.ADMIN_PLACE_ID = la.ADMIN_PLACE_ID
join xdf.xdf_ROAD_LINK rl
on la.LINK_ID = rl.LINK_ID
join xdf.xdf_ROAD_NAME rn
on rl.ROAD_NAME_ID = rn.ROAD_NAME_ID
where rl.IS_EXIT_NAME = 'N'
and rl.IS_JUNCTION_NAME = 'N'
and rn.ROAD_NAME_ID between 158348561 and
158348660
order by rl.ROAD_NAME_ID, ah.ORDER8_ID,
ah.BUILTUP_ID, rl.LINK_ID;
On Postgresql 8.4.1
Sort (cost=129346.71..129498.64 rows=60772 width=61) (actual
time=100.358..100.496 rows=1444 loops=1)
Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id,
rl.link_id
Sort Method: quicksort Memory: 252kB
-> Hash Join (cost=2603.57..124518.03 rows=60772 width=61)
(actual time=62.359..97.268 rows=1444 loops=1)
Hash Cond: (la.admin_place_id = ah.admin_place_id)
-> Nested Loop (cost=6.82..120781.81 rows=60772
width=57) (actual time=0.318..33.600 rows=1444 loops=1)
-> Nested Loop (cost=6.82..72383.98 rows=21451
width=51) (actual time=0.232..12.359 rows=722 loops=1)
-> Index Scan using pk_xdf_road_name on
xdf_road_name rn (cost=0.00..11.24 rows=97 width=21) (actual time=0.117..0.185
rows=100 loops=1)
Index Cond: ((road_name_id >=
158348561) AND (road_name_id <= 158348660))
-> Bitmap Heap Scan on xdf_road_link rl
(cost=6.82..743.34 rows=222 width=34) (actual time=0.025..0.115 rows=7
loops=100)
Recheck Cond: (rl.road_name_id =
rn.road_name_id)
Filter: ((rl.is_exit_name =
'N'::bpchar) AND (rl.is_junction_name = 'N'::bpchar))
-> Bitmap Index Scan on
nx_xdfroadlink_roadnameid (cost=0.00..6.76 rows=222 width=0) (actual
time=0.008..0.008 rows=7 loops=100)
Index Cond: (rl.road_name_id =
rn.road_name_id)
-> Index Scan using nx_xdflinkadmin_linkid on
xdf_link_admin la (cost=0.00..2.22 rows=3 width=10) (actual time=0.023..0.028
rows=2 loops=722)
Index Cond: (la.link_id = rl.link_id)
-> Hash (cost=1544.11..1544.11 rows=84211 width=12)
(actual time=61.924..61.924 rows=84211 loops=1)
-> Seq Scan on xdf_admin_hierarchy ah
(cost=0.00..1544.11 rows=84211 width=12) (actual time=0.017..33.442 rows=84211
loops=1)
Total runtime: 101.446 ms
and on Postgresql 8.3.8:
Sort (cost=3792.75..3792.95 rows=81 width=61) (actual
time=28.928..29.074 rows=1444 loops=1)
Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id,
rl.link_id
Sort Method: quicksort Memory: 252kB
-> Nested Loop (cost=21.00..3790.18 rows=81 width=61)
(actual time=0.210..26.098 rows=1444 loops=1)
-> Nested Loop (cost=21.00..3766.73 rows=81 width=57)
(actual time=0.172..19.148 rows=1444 loops=1)
-> Nested Loop (cost=21.00..3733.04 rows=14
width=51) (actual time=0.129..6.126 rows=722 loops=1)
-> Index Scan using pk_xdf_road_name on
xdf_road_name rn (cost=0.00..8.32 rows=1 width=21) (actual time=0.059..0.117
rows=100 loops=1)
Index Cond: ((road_name_id >=
158348561) AND (road_name_id <= 158348660))
-> Bitmap Heap Scan on xdf_road_link rl
(cost=21.00..3711.97 rows=1020 width=34) (actual time=0.015..0.055 rows=7
loops=100)
Recheck Cond: (rl.road_name_id =
rn.road_name_id)
Filter: ((rl.is_exit_name =
'N'::bpchar) AND (rl.is_junction_name = 'N'::bpchar))
-> Bitmap Index Scan on
nx_xdfroadlink_roadnameid (cost=0.00..20.75 rows=1020 width=0) (actual
time=0.007..0.007 rows=7 loops=100)
Index Cond: (rl.road_name_id =
rn.road_name_id)
-> Index Scan using nx_xdflinkadmin_linkid on
xdf_link_admin la (cost=0.00..2.31 rows=8 width=10) (actual time=0.014..0.017
rows=2 loops=722)
Index Cond: (la.link_id = rl.link_id)
-> Index Scan using pk_xdf_admin_hierarchy on
xdf_admin_hierarchy ah (cost=0.00..0.28 rows=1 width=12) (actual
time=0.003..0.004 rows=1 loops=1444)
Index Cond: (ah.admin_place_id = la.admin_place_id)
Total runtime: 29.366 ms
Hope this gives any clue. Or did I missunderstand you?
Regards
David
>-----Ursprüngliche Nachricht-----
>Von: Andres Freund [mailto:[email protected]]
>Gesendet: Dienstag, 8. Dezember 2009 00:25
>An: [email protected]
>Cc: Schmitz, David
>Betreff: Re: [PERFORM] performance penalty between Postgresql
>8.3.8 and 8.4.1
>
>Hi David,
>
>On Monday 07 December 2009 23:05:14 Schmitz, David wrote:
>> With our data it is a performance difference from 1h16min
>(8.3.8) to
>> 2h43min (8.4.1)
>Can you afford a explain analyze run overnight or so for both?
>
>Andres
>
Your output shows that the xdf_admin_hierarchy tables between versions
are drastically different. 8.3.8 only contains 1 row, whereas 8.4.1 contains
84211 rows.
Thom
*******************************************
innovative systems GmbH Navigation-Multimedia
Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser
Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980
*******************************************
Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte
Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und
loeschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe
dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are
not the intended recipient (or have received this e-mail in error) please
notify the sender immediately and delete this e-mail. Any unauthorized copying,
disclosure or distribution of the contents in this e-mail is strictly forbidden.
*******************************************
