Re: performance penalty between Postgresql 8.3.8 and 8.4.1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux