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

performance penalty between Postgresql 8.3.8 and 8.4.1

From: "Schmitz, David" <david(dot)schmitz(at)harman(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: performance penalty between Postgresql 8.3.8 and 8.4.1
Date: 2009-12-07 22:05:14
Message-ID: 02FE2F38DEB0714EACA6ADD491B2C018230258@OEKAW2EXVS04.hbi.ad.harman.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello everybody,

we have severe performance penalty between Postgresql 8.3.8 and 8.4.1

Consider the following tables:

CREATE TABLE xdf.xdf_admin_hierarchy
(
  admin_place_id integer NOT NULL,
  admin_order smallint NOT NULL,
  iso_country_code character(3) NOT NULL,
  country_id integer NOT NULL,
  order1_id integer,
  order2_id integer,
  order8_id integer,
  builtup_id integer,
  num_links integer,
  CONSTRAINT pk_xdf_admin_hierarchy PRIMARY KEY (admin_place_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE xdf.xdf_admin_hierarchy OWNER TO frog;

CREATE TABLE xdf.xdf_link_admin
(
  admin_place_id integer NOT NULL,
  link_id integer NOT NULL,
  side character(1) NOT NULL,
  CONSTRAINT pk_xdf_link_admin PRIMARY KEY (link_id, side)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE xdf.xdf_link_admin OWNER TO frog;

CREATE INDEX nx_xdflinkadmin_adminplaceid
  ON xdf.xdf_link_admin
  USING btree
  (admin_place_id);

CREATE INDEX nx_xdflinkadmin_linkid
  ON xdf.xdf_link_admin
  USING btree
  (link_id);

CREATE TABLE xdf.xdf_road_link
(
  road_link_id integer NOT NULL,
  road_name_id integer,
  left_address_range_id integer NOT NULL,
  right_address_range_id integer NOT NULL,
  address_type smallint NOT NULL,
  is_exit_name character(1) NOT NULL,
  explicatable character(1) NOT NULL,
  is_junction_name character(1) NOT NULL,
  is_name_on_roadsign character(1) NOT NULL,
  is_postal_name character(1) NOT NULL,
  is_stale_name character(1) NOT NULL,
  is_vanity_name character(1) NOT NULL,
  is_scenic_name character(1) NOT NULL,
  link_id integer NOT NULL,
  CONSTRAINT pk_xdf_road_link PRIMARY KEY (road_link_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE xdf.xdf_road_link OWNER TO frog;

CREATE INDEX nx_xdfroadlink_leftaddressrangeid
  ON xdf.xdf_road_link
  USING btree
  (left_address_range_id);

CREATE INDEX nx_xdfroadlink_linkid
  ON xdf.xdf_road_link
  USING btree
  (link_id);

CREATE INDEX nx_xdfroadlink_rightaddressrangeid
  ON xdf.xdf_road_link
  USING btree
  (right_address_range_id);

CREATE INDEX nx_xdfroadlink_roadnameid
  ON xdf.xdf_road_link
  USING btree
  (road_name_id);

CREATE TABLE xdf.xdf_road_name
(
  road_name_id integer NOT NULL,
  route_type smallint NOT NULL,
  attached_to_base character(1) NOT NULL,
  precedes_base character(1) NOT NULL,
  prefix character varying(10),
  street_type character varying(30),
  suffix character varying(2),
  base_name character varying(60) NOT NULL,
  language_code character(3) NOT NULL,
  is_exonym character(1) NOT NULL,
  name_type character(1) NOT NULL,
  direction_on_sign character(1) NOT NULL,
  street_name character varying(60) NOT NULL,
  CONSTRAINT pk_xdf_road_name PRIMARY KEY (road_name_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE xdf.xdf_road_name OWNER TO frog;

CREATE INDEX nx_xdfroadname_languagecode
  ON xdf.xdf_road_name
  USING btree
  (language_code);

If one executes a query of the following structure:

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 xdf.xdf_ADMIN_HIERARCHY AH, xdf.xdf_LINK_ADMIN LA,
xdf.xdf_ROAD_LINK RL, xdf.xdf_ROAD_NAME RN
WHERE AH.ADMIN_PLACE_ID = LA.ADMIN_PLACE_ID
AND LA.LINK_ID = RL.LINK_ID
AND RL.ROAD_NAME_ID = RN.ROAD_NAME_ID
AND 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;
               
It is carried out with poor performance on postgresql 8.4.1 However postgresql 8.3.8 performs just fine.
If you take a closer look at the query with EXPLAIN, it becomes obvious, that postgresql 8.4 does not
consider the primary key at level 3 and instead generates a hash join:

Postgresql 8.4.1:

Sort  (cost=129346.71..129498.64 rows=60772 width=61)
  Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id, rl.link_id
  ->  Hash Join  (cost=2603.57..124518.03 rows=60772 width=61)
        Hash Cond: (la.admin_place_id = ah.admin_place_id)
        ->  Nested Loop  (cost=6.82..120781.81 rows=60772 width=57)
              ->  Nested Loop  (cost=6.82..72383.98 rows=21451 width=51)
                    ->  Index Scan using pk_rdf_road_name on rdf_road_name rn  (cost=0.00..11.24 rows=97 width=21)
                          Index Cond: ((road_name_id >= 158348561) AND (road_name_id <= 158348660))
                    ->  Bitmap Heap Scan on rdf_road_link rl  (cost=6.82..743.34 rows=222 width=34)
                          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_rdfroadlink_roadnameid  (cost=0.00..6.76 rows=222 width=0)
                                Index Cond: (rl.road_name_id = rn.road_name_id)
              ->  Index Scan using nx_rdflinkadmin_linkid on rdf_link_admin la  (cost=0.00..2.22 rows=3 width=10)
                    Index Cond: (la.link_id = rl.link_id)
        ->  Hash  (cost=1544.11..1544.11 rows=84211 width=12)
              ->  Seq Scan on rdf_admin_hierarchy ah  (cost=0.00..1544.11 rows=84211 width=12)

Postgresql 8.3.8:

Sort  (cost=3792.75..3792.95 rows=81 width=61)
  Sort Key: rl.road_name_id, ah.order8_id, ah.builtup_id, rl.link_id
  ->  Nested Loop  (cost=21.00..3790.18 rows=81 width=61)
        ->  Nested Loop  (cost=21.00..3766.73 rows=81 width=57)
              ->  Nested Loop  (cost=21.00..3733.04 rows=14 width=51)
                    ->  Index Scan using pk_rdf_road_name on rdf_road_name rn  (cost=0.00..8.32 rows=1 width=21)
                          Index Cond: ((road_name_id >= 158348561) AND (road_name_id <= 158348660))
                    ->  Bitmap Heap Scan on rdf_road_link rl  (cost=21.00..3711.97 rows=1020 width=34)
                          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_rdfroadlink_roadnameid  (cost=0.00..20.75 rows=1020 width=0)
                                Index Cond: (rl.road_name_id = rn.road_name_id)
              ->  Index Scan using nx_rdflinkadmin_linkid on rdf_link_admin la  (cost=0.00..2.31 rows=8 width=10)
                    Index Cond: (la.link_id = rl.link_id)
        ->  Index Scan using pk_rdf_admin_hierarchy on rdf_admin_hierarchy ah  (cost=0.00..0.28 rows=1 width=12)
              Index Cond: (ah.admin_place_id = la.admin_place_id)

With our data it is a performance difference from 1h16min (8.3.8) to 2h43min (8.4.1)

I hope someone can help me out with my problem. If you need further information please let me know.

Mit freundlichem Gruß / Best regards

David Schmitz
Dipl.-Ing.(FH)
Software Developer New Map Compiler

HARMAN/BECKER AUTOMOTIVE SYSTEMS
innovative systems GmbH
Hugh-Greene-Weg 2-4 - 22529 Hamburg - Germany
Phone: +49 (0)40-30067-990
Fax:     +49 (0)40-30067-969
Mailto:DaSchmitz(at)harmanbecker(dot)com 
 
*******************************************
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.
*******************************************

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-12-07 22:19:51
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Previous:From: Craig JamesDate: 2009-12-07 21:53:45
Subject: Re: RAID card recommendation

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