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 isnt 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.0,
cf_account_id int4 NOT NULL DEFAULT 0,
cost numeric(22,9) NOT NULL DEFAULT 0.0,
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.0,
bid numeric(22,9) NOT NULL DEFAULT 0.0,
ask numeric(22,9) NOT NULL DEFAULT 0.0,
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
