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