pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Sebastian Böck <sebastianboeck@freenet.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing Outer Joins
Date: Thu, 27 Jan 2005 10:18:35 +0000
Message-ID: <41F8BFFB.5020605@archonet.com> (raw)
In-Reply-To: <41F8A41A.5020301@freenet.de>
References: <41F8A41A.5020301@freenet.de>

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



view thread (3+ messages)  latest in thread

Message-ID: <41F8BFFB.5020605@archonet.com>
Permalink:  ../41F8BFFB.5020605@archonet.com/
Also on:    postgresql.org/message-id/41F8BFFB.5020605@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, sebastianboeck@freenet.de
  Subject: Re: Optimizing Outer Joins
  In-Reply-To: <41F8BFFB.5020605@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox