Обсуждение: Left Join Performance vs Inner Join Performance

Поиск
Список
Период
Сортировка

Left Join Performance vs Inner Join Performance

От
"Dave Dutcher"
Дата:

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

 

Re: Left Join Performance vs Inner Join Performance

От
Tom Lane
Дата:
"Dave Dutcher" <dave@tridecap.com> writes:
> I have an inner join query that runs fast, but I when I change to a left
> join the query runs 96 times slower.

This looks like an issue that is fixed in the latest set of releases,
namely that OUTER JOIN ON conditions that reference only the inner
side of the join weren't getting pushed down into indexquals.  See
thread here:
http://archives.postgresql.org/pgsql-performance/2005-12/msg00134.php
and patches in this and the following messages:
http://archives.postgresql.org/pgsql-committers/2005-12/msg00105.php

            regards, tom lane