Skip site navigation (1) Skip section navigation (2)

Re: performance penalty between Postgresql 8.3.8 and 8.4.1

From: "Schmitz, David" <david(dot)schmitz(at)harman(dot)com>
To: "Thom Brown" <thombrown(at)gmail(dot)com>
Cc: "Andres Freund" <andres(at)anarazel(dot)de>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Date: 2009-12-08 10:18:45
Message-ID: 02FE2F38DEB0714EACA6ADD491B2C01802FA175D@OEKAW2EXVS04.hbi.ad.harman.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
*******************************************

In response to

pgsql-performance by date

Next:From: Andres FreundDate: 2009-12-08 10:28:55
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Previous:From: Thom BrownDate: 2009-12-08 10:11:32
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group