X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 32E049DC832 for ; Tue, 10 Jan 2006 23:06:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65339-03 for ; Tue, 10 Jan 2006 23:06:13 -0400 (AST) X-Greylist: delayed 00:59:58.586091 by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id E07459DC812 for ; Tue, 10 Jan 2006 23:06:05 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [63.226.156.118] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 81113492 for pgsql-performance@postgresql.org; Tue, 10 Jan 2006 18:06:09 -0800 From: "Dave Dutcher" To: Subject: Left Join Performance vs Inner Join Performance Date: Tue, 10 Jan 2006 20:06:08 -0600 Message-ID: <011f01c61653$96c509f0$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0120_01C61621.4C2A99F0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/96 X-Sequence-Number: 16574 This is a multi-part message in MIME format. ------=_NextPart_000_0120_01C61621.4C2A99F0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hello, I have an inner join query that runs fast, but I when I change to a left join the query runs 96 times slower. I wish I could always do an inner join, but there are rare times when there isn't data in the right hand table. I could expect a small performance hit, but the difference is so large I figure I must be doing something wrong. What I think is the strangest is how similar the two query plans are. Query (inner join version, just replace inner with left for other version): select p.owner_trader_id, p.strategy_id, m.last, m.bid, m.ask from om_position p inner join om_instrument_mark m on m.instrument_id = p.instrument_id and m.data_source_id = 5 and m.date = '2005-02-03' where p.as_of_date = '2005-02-03' and p.fund_id = 'TRIDE' and p.owner_trader_id = 'tam4' and p.strategy_id = 'BASKET1' Query plan for inner join: Nested Loop (cost=0.00..176.99 rows=4 width=43) (actual time=0.234..14.182 rows=193 loops=1) -> Index Scan using as_of_date_om_position_index on om_position p (cost=0.00..68.26 rows=19 width=20) (actual time=0.171..5.210 rows=193 loops=1) Index Cond: (as_of_date = '2005-02-03'::date)" Filter: (((fund_id)::text = 'TRIDE'::text) AND ((owner_trader_id)::text = 'tam4'::text) AND ((strategy_id)::text = 'BASKET1'::text)) -> Index Scan using om_instrument_mark_pkey on om_instrument_mark m (cost=0.00..5.71 rows=1 width=31) (actual time=0.028..0.032 rows=1 loops=193) Index Cond: ((m.instrument_id = "outer".instrument_id) AND (m.data_source_id = 5) AND (m.date = '2005-02-03'::date)) Total runtime: 14.890 ms Query plan for left join: Nested Loop Left Join (cost=0.00..7763.36 rows=19 width=43) (actual time=3.005..1346.308 rows=193 loops=1) -> Index Scan using as_of_date_om_position_index on om_position p (cost=0.00..68.26 rows=19 width=20) (actual time=0.064..6.654 rows=193 loops=1) Index Cond: (as_of_date = '2005-02-03'::date) Filter: (((fund_id)::text = 'TRIDE'::text) AND ((owner_trader_id)::text = 'tam4'::text) AND ((strategy_id)::text = 'BASKET1'::text)) -> Index Scan using om_instrument_mark_pkey on om_instrument_mark m (cost=0.00..404.99 rows=1 width=31) (actual time=3.589..6.919 rows=1 loops=193) Index Cond: (m.instrument_id = "outer".instrument_id) Filter: ((data_source_id = 5) AND (date = '2005-02-03'::date)) Total runtime: 1347.159 ms Table Definitions: CREATE TABLE om_position ( fund_id varchar(10) NOT NULL DEFAULT ''::character varying, owner_trader_id varchar(10) NOT NULL DEFAULT ''::character varying, strategy_id varchar(30) NOT NULL DEFAULT ''::character varying, instrument_id int4 NOT NULL DEFAULT 0, as_of_date date NOT NULL DEFAULT '0001-01-01'::date, pos numeric(22,9) NOT NULL DEFAULT 0.000000000, cf_account_id int4 NOT NULL DEFAULT 0, cost numeric(22,9) NOT NULL DEFAULT 0.000000000, CONSTRAINT om_position_pkey PRIMARY KEY (fund_id, owner_trader_id, strategy_id, cf_account_id, instrument_id, as_of_date), CONSTRAINT "$1" FOREIGN KEY (strategy_id) REFERENCES om_strategy (strategy_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$2" FOREIGN KEY (fund_id) REFERENCES om_fund (fund_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$3" FOREIGN KEY (cf_account_id) REFERENCES om_cf_account (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$4" FOREIGN KEY (owner_trader_id) REFERENCES om_trader (trader_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH OIDS; CREATE INDEX as_of_date_om_position_index ON om_position USING btree (as_of_date); CREATE TABLE om_instrument_mark ( instrument_id int4 NOT NULL DEFAULT 0, data_source_id int4 NOT NULL DEFAULT 0, date date NOT NULL DEFAULT '0001-01-01'::date, "last" numeric(22,9) NOT NULL DEFAULT 0.000000000, bid numeric(22,9) NOT NULL DEFAULT 0.000000000, ask numeric(22,9) NOT NULL DEFAULT 0.000000000, "comment" varchar(150) NOT NULL DEFAULT ''::character varying, trader_id varchar(10) NOT NULL DEFAULT 'auto'::character varying, CONSTRAINT om_instrument_mark_pkey PRIMARY KEY (instrument_id, data_source_id, date), CONSTRAINT "$1" FOREIGN KEY (instrument_id) REFERENCES om_instrument (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$2" FOREIGN KEY (data_source_id) REFERENCES om_data_source (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT om_instrument_mark_trader_id_fkey FOREIGN KEY (trader_id) REFERENCES om_trader (trader_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH OIDS; Thanks for any help ------=_NextPart_000_0120_01C61621.4C2A99F0 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hello,

 

I have an inner join query that runs fast, but I when = I change to a left join the query runs 96 times = slower.  I wish I could always do an = inner join, but there are rare times when there isn’t data in the right hand table.  I could expect a = small performance hit, but the difference is so large I figure I must be doing something wrong.  What I = think is the strangest is how similar the two query plans = are.

 

Query (inner join version, just replace inner with = left for other version):

select<= font size=3D2 face=3DArial> =

p.owner_trader_id, p.strategy_id, m.last, = m.bid, m.ask

from =

om_position p inner join om_instrument_mark m on m.instrument_id =3D p.instrument_id and m.data_source_id =3D 5 and m.date =3D '2005-02-03' =

where p.as_of_date =3D '2005-02-03' and p.fund_id =3D 'TRIDE' and p.owner_trader_id =3D 'tam4' = and p.strategy_id =3D = 'BASKET1'

 

Query plan for inner = join:

Nested Loop  (cost=3D0.00..176.99 = rows=3D4 width=3D43) (actual time=3D0.234..14.182 rows=3D193 = loops=3D1)

  ->  = Index Scan using as_of_date_om_position_index on om_position p  (cost=3D0.00..68.26 rows=3D19 width=3D20) (actual = time=3D0.171..5.210 rows=3D193 loops=3D1)

        = Index Cond: (as_of_date =3D '2005-02-03'::date)"

        Filter: (((fund_id)::text =3D 'TRIDE'::text) AND ((owner_trader_id)::text =3D 'tam4'::text) AND ((strategy_id)::text = =3D 'BASKET1'::text))

  ->  = Index Scan using om_instrument_mark_pkey on om_instrument_mark m  (cost=3D0.00..5.71 = rows=3D1 width=3D31) (actual time=3D0.028..0.032 rows=3D1 = loops=3D193)

        Index Cond: ((m.instrument_id =3D "outer".instrument_id) AND = (m.data_source_id =3D 5) AND (m.date =3D '2005-02-03'::date))

Total runtime: 14.890 ms

 

Query plan for left = join:

Nested Loop Left Join  (cost=3D0.00..7763.36 = rows=3D19 width=3D43) (actual time=3D3.005..1346.308 rows=3D193 = loops=3D1)

  ->  = Index Scan using as_of_date_om_position_index on om_position p  (cost=3D0.00..68.26 rows=3D19 width=3D20) (actual = time=3D0.064..6.654 rows=3D193 loops=3D1)

        Index Cond: (as_of_date =3D '2005-02-03'::date)

        Filter: (((fund_id)::text =3D 'TRIDE'::text) AND ((owner_trader_id)::text =3D 'tam4'::text) AND ((strategy_id)::text = =3D 'BASKET1'::text))

  ->  = Index Scan using om_instrument_mark_pkey on om_instrument_mark m  (cost=3D0.00..404.99 = rows=3D1 width=3D31) (actual time=3D3.589..6.919 rows=3D1 = loops=3D193)

        Index Cond: (m.instrument_id =3D "outer".instrument_id)=

        Filter: ((data_source_id =3D 5) AND = (date =3D '2005-02-03'::date))

Total runtime: 1347.159 = ms

 

 

Table Definitions:

CREATE TABLE om_position

(

  fund_id varchar(10) NOT NULL DEFAULT ''::character varying,

  owner_trader_id varchar(10) NOT NULL DEFAULT ''::character = varying,

  strategy_id varchar(30) NOT NULL DEFAULT ''::character varying,

  instrument_id int4 NOT NULL DEFAULT = 0,

  as_of_date date NOT = NULL DEFAULT '0001-01-01'::date,

  pos numeric(22,9) NOT NULL DEFAULT = 0.000000000,

  cf_account_id int4 NOT NULL DEFAULT = 0,

  cost numeric(22,9) NOT NULL DEFAULT = 0.000000000,

  = CONSTRAINT om_position_pkey PRIMARY KEY (fund_id, owner_trader_id, strategy_id, cf_account_id, instrument_id, as_of_date),

  = CONSTRAINT "$1" FOREIGN KEY (strategy_id)

      = REFERENCES om_strategy (strategy_id) MATCH SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION,

  = CONSTRAINT "$2" FOREIGN KEY (fund_id)

      = REFERENCES om_fund (fund_id) = MATCH SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION,

  = CONSTRAINT "$3" FOREIGN KEY (cf_account_id)

      = REFERENCES om_cf_account (id) MATCH = SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION,

  = CONSTRAINT "$4" FOREIGN KEY (owner_trader_id)

      = REFERENCES om_trader (trader_id) = MATCH SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION

)

WITH OIDS;

CREATE INDEX as_of_date_om_position_index

  ON = om_position

  USING = btree

  (as_of_date);

 

CREATE TABLE om_instrument_mark

(

  instrument_id int4 NOT NULL DEFAULT = 0,

  data_source_id int4 NOT NULL DEFAULT = 0,

  date date NOT NULL = DEFAULT '0001-01-01'::date,

  = "last" numeric(22,9) NOT NULL DEFAULT = 0.000000000,

  bid numeric(22,9) NOT NULL DEFAULT = 0.000000000,

  ask numeric(22,9) NOT NULL DEFAULT = 0.000000000,

  = "comment" varchar(150) NOT NULL DEFAULT ''::character varying,

  trader_id varchar(10) NOT NULL DEFAULT 'auto'::character = varying,

  = CONSTRAINT om_instrument_mark_pkey PRIMARY KEY (instrument_id, data_source_id, = date),

  = CONSTRAINT "$1" FOREIGN KEY (instrument_id)

      = REFERENCES om_instrument (id) MATCH = SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION,

  = CONSTRAINT "$2" FOREIGN KEY (data_source_id)

      = REFERENCES om_data_source (id) MATCH = SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION,

  = CONSTRAINT om_instrument_mark_trader_id_fkey FOREIGN KEY = (trader_id)

      = REFERENCES om_trader (trader_id) = MATCH SIMPLE

      ON = UPDATE NO ACTION ON DELETE NO ACTION

)

WITH OIDS;

 

Thanks for any help

 

------=_NextPart_000_0120_01C61621.4C2A99F0--