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: 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:42:01
Message-ID: 02FE2F38DEB0714EACA6ADD491B2C01802FA17A1@OEKAW2EXVS04.hbi.ad.harman.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Andres,

this is just one of many of these queries. There are a lot of jobs calculating 
stuff for different ranges which are defined via between in the where clause.


When I leave out the between in the where clause it returns:

On Postgresql 8.4.1:

Sort  (cost=5390066.42..5435347.78 rows=18112546 width=61) (actual time=84382.275..91367.983 rows=12742796 loops=1)
Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id, rl.link_id
Sort Method:  external merge  Disk: 924536kB
->  Hash Join  (cost=1082249.40..2525563.48 rows=18112546 width=61) (actual time=23367.205..52256.209 rows=12742796 loops=1)
Hash Cond: (la.admin_place_id = ah.admin_place_id)
->  Merge Join  (cost=1079652.65..2183356.50 rows=18112546 width=57) (actual time=23306.643..45541.157 rows=12742796 loops=1)
      Merge Cond: (la.link_id = rl.link_id)
      ->  Index Scan using nx_xdflinkadmin_linkid on xdf_link_admin la  (cost=0.00..798398.53 rows=16822372 width=10) (actual time=0.098..12622.576 rows=16822399 loops=1)
      ->  Sort  (cost=1071304.95..1087287.81 rows=6393147 width=51) (actual time=23302.596..25640.559 rows=12742795 loops=1)
	    Sort Key: rl.link_id
	    Sort Method:  external sort  Disk: 405896kB
	    ->  Hash Join  (cost=15735.91..348620.58 rows=6393147 width=51) (actual time=327.064..9189.938 rows=6371398 loops=1)
		  Hash Cond: (rl.road_name_id = rn.road_name_id)
		  ->  Seq Scan on xdf_road_link rl  (cost=0.00..182236.41 rows=7708159 width=34) (actual time=0.028..2689.085 rows=7709085 loops=1)
			Filter: ((is_exit_name = 'N'::bpchar) AND (is_junction_name = 'N'::bpchar))
		  ->  Hash  (cost=9885.96..9885.96 rows=467996 width=21) (actual time=326.740..326.740 rows=467996 loops=1)
			->  Seq Scan on xdf_road_name rn  (cost=0.00..9885.96 rows=467996 width=21) (actual time=0.019..191.473 rows=467996 loops=1)
->  Hash  (cost=1544.11..1544.11 rows=84211 width=12) (actual time=60.453..60.453 rows=84211 loops=1)
      ->  Seq Scan on xdf_admin_hierarchy ah  (cost=0.00..1544.11 rows=84211 width=12) (actual time=0.019..31.723 rows=84211 loops=1)
Total runtime: 92199.676 ms

On Postgresql 8.3.8:

Sort  (cost=9419546.57..9514635.57 rows=38035597 width=61) (actual time=82790.473..88847.963 rows=12742796 loops=1)
  Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id, rl.link_id
  Sort Method:  external merge  Disk: 999272kB
  ->  Hash Join  (cost=1079404.97..3200652.85 rows=38035597 width=61) (actual time=22583.059..51197.249 rows=12742796 loops=1)
        Hash Cond: (la.admin_place_id = ah.admin_place_id)
        ->  Merge Join  (cost=1076808.22..2484888.66 rows=38035597 width=57) (actual time=22524.015..44539.246 rows=12742796 loops=1)
              Merge Cond: (la.link_id = rl.link_id)
              ->  Index Scan using nx_xdflinkadmin_linkid on xdf_link_admin la  (cost=0.00..795583.17 rows=16822420 width=10) (actual time=0.086..11725.990 rows=16822399 loops=1)
              ->  Sort  (cost=1076734.49..1092821.79 rows=6434920 width=51) (actual time=22514.553..25083.253 rows=12742795 loops=1)
                    Sort Key: rl.link_id
                    Sort Method:  external sort  Disk: 443264kB
                    ->  Hash Join  (cost=15743.47..349025.77 rows=6434920 width=51) (actual time=330.211..9014.353 rows=6371398 loops=1)
                          Hash Cond: (rl.road_name_id = rn.road_name_id)
                          ->  Seq Scan on xdf_road_link rl  (cost=0.00..182235.08 rows=7706491 width=34) (actual time=0.018..2565.983 rows=7709085 loops=1)
                                Filter: ((is_exit_name = 'N'::bpchar) AND (is_junction_name = 'N'::bpchar))
                          ->  Hash  (cost=9890.43..9890.43 rows=468243 width=21) (actual time=329.906..329.906 rows=467996 loops=1)
                                ->  Seq Scan on xdf_road_name rn  (cost=0.00..9890.43 rows=468243 width=21) (actual time=0.018..190.764 rows=467996 loops=1)
        ->  Hash  (cost=1544.11..1544.11 rows=84211 width=12) (actual time=58.910..58.910 rows=84211 loops=1)
              ->  Seq Scan on xdf_admin_hierarchy ah  (cost=0.00..1544.11 rows=84211 width=12) (actual time=0.009..28.725 rows=84211 loops=1)
Total runtime: 89612.801 ms

Regards 

David 

>-----Urspr√ľngliche Nachricht-----
>Von: Andres Freund [mailto:andres(at)anarazel(dot)de] 
>Gesendet: Dienstag, 8. Dezember 2009 11:29
>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 Tuesday 08 December 2009 10:59:51 Schmitz, David wrote:
>> >> With our data it is a performance difference from 1h16min
>> >> (8.3.8) to 2h43min (8.4.1)
>> On Postgresql 8.4.1
>> Total runtime: 101.446 ms
>> and on Postgresql  8.3.8:
>> Total runtime: 29.366 ms
>Hm. There obviously is more going on than these queries?
>
>> Hash Join  (cost=2603.57..124518.03 rows=60772 width=61) (actual
>time=62.359..97.268 rows=1444 loops=1)
>> Nested Loop  (cost=21.00..3790.18 rows=81 width=61) (actual
>time=0.210..26.098 rows=1444 loops=1)
>Both misestimate the resultset quite a bit. It looks like 
>happenstance that the one on 8.3 turns out to be better...
>
>Andres
> 
 
*******************************************
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: Craig RingerDate: 2009-12-08 12:12:04
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Previous:From: Andres FreundDate: 2009-12-08 10:28:55
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