pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft Join Performance vs Inner Join Performance
2+ messages / 2 participants
[nested] [flat]
* Left Join Performance vs Inner Join Performance
@ 2006-01-11 02:06 Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dave Dutcher @ 2006-01-11 02:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Left Join Performance vs Inner Join Performance
@ 2006-01-11 04:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-01-11 04:38 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-11 04:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-11 02:06 Left Join Performance vs Inner Join Performance Dave Dutcher <dave@tridecap.com>
2006-01-11 04:38 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox