Re: performance penalty between Postgresql 8.3.8 and 8.4.1

"Schmitz, David" <david.schmitz@xxxxxxxxxx> · Tue, 8 Dec 2009 11:18:45 +0100

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@xxxxxxxxx] 

Gesendet: Dienstag, 8. Dezember 2009 11:12
An: Schmitz, 
  David
Cc: Andres Freund; 
  pgsql-performance@xxxxxxxxxxxxxx
Betreff: Re:  performance 
  penalty between Postgresql 8.3.8 and 8.4.1

  2009/12/8 Schmitz, David <david.schmitz@xxxxxxxxxx>

  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@xxxxxxxxxxx]
>Gesendet: 
    Dienstag, 8. Dezember 2009 00:25
>An: pgsql-performance@xxxxxxxxxxxxxx
>Cc: 
    Schmitz, David
>Betreff: Re:  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.
*******************************************