Left Join Performance vs Inner Join 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