Re: performance penalty between Postgresql 8.3.8 and 8.4.1

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:thombrown(at)gmail(dot)com]
Gesendet: Dienstag, 8. Dezember 2009 11:12
An: Schmitz, David
Cc: Andres Freund; pgsql-performance(at)postgresql(dot)org
Betreff: Re: [PERFORM] performance penalty between Postgresql 8.3.8 and 8.4.1


2009/12/8 Schmitz, David <david(dot)schmitz(at)harman(dot)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:andres(at)anarazel(dot)de]
>Gesendet: Dienstag, 8. Dezember 2009 00:25
>An: pgsql-performance(at)postgresql(dot)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

*******************************************
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.
*******************************************