Optimizing Outer Joins

From: Sebastian Böck <sebastianboeck(at)freenet(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizing Outer Joins
Date: 2005-01-27 08:19:38
Message-ID: 41F8A41A.5020301@freenet.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

if i have the following (simple) table layout:

create table a (
id serial primary key
);

create table b (
id integer references a,
test text
);

create view c as
select a.id,b.test from a
left join b
on a.id = b.id;

So if i do a select * from c i get the following:

test=# EXPLAIN SELECT * from g;
QUERY PLAN
----------------------------------------------------------------
Hash Left Join (cost=2.45..8.91 rows=8 width=36)
Hash Cond: ("outer".id = "inner".id)
-> Seq Scan on a (cost=0.00..1.08 rows=8 width=4)
-> Hash (cost=2.16..2.16 rows=116 width=36)
-> Seq Scan on b (cost=0.00..2.16 rows=116 width=36)

and a select id from c executes as

test=# EXPLAIN SELECT id from g;
QUERY PLAN
---------------------------------------------------------------
Hash Left Join (cost=2.45..7.02 rows=8 width=4)
Hash Cond: ("outer".id = "inner".id)
-> Seq Scan on a (cost=0.00..1.08 rows=8 width=4)
-> Hash (cost=2.16..2.16 rows=116 width=4)
-> Seq Scan on b (cost=0.00..2.16 rows=116 width=4)

so the only difference is the width estimation.

But why is the scan on table b performed?
If i understand it correctly this is unnecessary because the
result contains only rows from table a.

Is there a way to tell postgres not to do the extra work.
My aim is to speed up lookup to complex joins.

Thanks

Sebastian

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message PFC 2005-01-27 09:54:25 Re: [SQL] OFFSET impact on Performance???
Previous Message Dustin Sallings 2005-01-27 08:02:29 Re: SQL Performance Guidelines