Skip site navigation (1) Skip section navigation (2)

Re: Optimizing Outer Joins

From: Richard Huxton <dev(at)archonet(dot)com>
To: Sebastian Böck <sebastianboeck(at)freenet(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizing Outer Joins
Date: 2005-01-27 10:18:35
Message-ID: 41F8BFFB.5020605@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Sebastian Böck wrote:
> 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;

> test=# EXPLAIN SELECT * from g;

> test=# EXPLAIN SELECT id from g;

> 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.

It's only unnecessary in the case where there is a 1:1 correspondence 
between a.id and b.id - if you had more than one matching row in "b" 
then there'd be repeated rows from "a" in the result. Not sure if PG can 
  tell what the situation is regarding references and pkeys, but in your 
example you don't have one anyway.

--
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Oleg BartunovDate: 2005-01-27 10:33:00
Subject: Re: [SQL] OFFSET impact on Performance???
Previous:From: PFCDate: 2005-01-27 09:54:25
Subject: Re: [SQL] OFFSET impact on Performance???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group