pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sebastian Böck <sebastianboeck@freenet.de>
To: pgsql-performance@postgresql.org
Subject: Optimizing Outer Joins
Date: Thu, 27 Jan 2005 09:19:38 +0100
Message-ID: <41F8A41A.5020301@freenet.de> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <41F8A41A.5020301@freenet.de>
Permalink:  ../41F8A41A.5020301@freenet.de/
Also on:    postgresql.org/message-id/41F8A41A.5020301@freenet.de

 · 

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: sebastianboeck@freenet.de
  Subject: Re: Optimizing Outer Joins
  In-Reply-To: <41F8A41A.5020301@freenet.de>

* 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