Re: performance penalty between Postgresql 8.3.8 and 8.4.1

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