Re: performance penalty between Postgresql 8.3.8 and 8.4.1 - Mailing list 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)
(actualtime=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=7709085loops=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=467996loops=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=84211loops=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=12742796loops=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=12742795loops=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=6371398loops=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.983rows=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=467996loops=1) 
                                ->  Seq Scan on xdf_road_name rn  (cost=0.00..9890.43 rows=468243 width=21) (actual
time=0.018..190.764rows=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.725rows=84211 loops=1) 
Total runtime: 89612.801 ms

Regards

David

>-----Ursprüngliche Nachricht-----
>Von: Andres Freund [mailto:andres@anarazel.de]
>Gesendet: Dienstag, 8. Dezember 2009 11:29
>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 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
sindoder 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
receivedthis e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying,
disclosureor distribution of the contents in this e-mail is strictly forbidden. 
*******************************************