Re: left outer join terrible slow compared to inner join

On Thu, Aug 28, 2003 at 02:10:15PM -0400, Tom Lane wrote:
> Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> writes:
> > Thanks for the suggestion, but the result is close to the original outer
> > join without the explicit cross join but far away from the speed of the
> > inner join.
>
> > EXPLAIN ANALYZE SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ( ot_adresse AS a CROSS JOIN ot_produkt
ASp) LEFT OUTER JOIN  ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE  p.a_id = a.id AND a.id = '105391105424941' AND
a.m_id= '37'; 
> > NOTICE:  QUERY PLAN:
>
> >               ->  Subquery Scan pz  (cost=0.00..1683.51 rows=40851 width=170) (actual time=0.03..485.07 rows=40917
loops=11)
> >                     ->  Seq Scan on o_kat_prod  (cost=0.00..1683.51 rows=40851 width=170) (actual time=0.02..281.77
rows=40917loops=11) 
>
> Hmm, I don't understand why ot_kat_prod is being treated as a subquery
> here.  It isn't a view or something is it?
Sorry, You're correct, actually this is a view of an other table called
o_kat_prod defined as
SELECT * FROM o_kat_prod;
but supposed to be in my next step something like
SELECT * FROM o_kat_prod WHERE <a_single_field> IS NOT NULL;

And doing the explicit cross join statement on o_kat_prod instead of
ot_kat_prod gives the expected performance to me ( 7.42 msec instead
of 7324.49 msec with EXPLAIN ANALYZE).

Do i've any chance to get the same performance on the view?

Thanks for any help!
-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.