2009/12/8 Schmitz, David <david.schm...@harman.com> > 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:and...@anarazel.de] > >Gesendet: Dienstag, 8. Dezember 2009 00:25 > >An: pgsql-performance@postgresql.org > >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
