inefficient query plan with left joined view

From: Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: inefficient query plan with left joined view
Date: 2003-01-28 16:50:58
Message-ID: 3E36B4F2.1020506@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

What I'm doing on V7.3.1:

select t1.keycol, t2.keycol
from tab1 t1
LEFT join myview t2 on t1.keycol=t2.keycol
where t1.keycol=1000001

t1 has 100 rows, t2 has 4000, both with keycol as PK.

the view is created as
CREATE myview AS SELECT keycol, 22::integer as calc_col FROM tab2

The query plan will show an ugly subquery scan on all tab2 rows. If the
view is created without calculated columns, the query plan looks as
expected showing and index scan on tab2 with the correct condition,
inner join will always be ok.

In real life, the view consists of a lot more tables, and the tables may
contain >1,000,000 rows so you may imagine the performance...

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-01-28 18:30:07 Re: inefficient query plan with left joined view
Previous Message Ron Johnson 2003-01-28 15:15:09 Re: Indexing foreign keys