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