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