Skip site navigation (1) Skip section navigation (2)

Re: performance penalty between Postgresql 8.3.8 and 8.4.1

From: Thom Brown <thombrown(at)gmail(dot)com>
To: "Schmitz, David" <david(dot)schmitz(at)harman(dot)com>
Cc: 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:11:32
Message-ID: bddc86150912080211w737ef0fct7891996cbd8456eb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2009/12/8 Schmitz, David <david(dot)schmitz(at)harman(dot)com>

> 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(at)anarazel(dot)de]
> >Gesendet: Dienstag, 8. Dezember 2009 00:25
> >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 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

In response to

Responses

pgsql-performance by date

Next:From: Schmitz, DavidDate: 2009-12-08 10:18:45
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Previous:From: Richard NeillDate: 2009-12-08 10:08:02
Subject: Re: Checkpoint spikes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group