pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Olbersen <dave@slickness.org>
To: pgsql-sql@postgresql.org
Subject: Please don't kill me!
Date: Wed, 24 Jan 2001 11:31:57 -0800 (PST)
Message-ID: <Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net> (raw)

I have two statements that accomplish the same task and I'm trying to decide
which to use. One uses a sub-select, and the other just does a few more joins.
I expect that giving the SELECT statement's themseleves won't get me much help, so here is the output of the EXPLAIN query that I ran on both of them. I read
the FAQ on EXPLAIN a bit but I'm still confused.

So could somebody help me understand why it appears as though the first query
will run much faster (?) than the second?

--snip!--

Nested Loop  (cost=81.80..114.17 rows=33 width=68)
  InitPlan
    ->  Seq Scan on l_portal_statuses  (cost=0.00..22.50 rows=10 width=4)
  ->  Merge Join  (cost=81.80..86.63 rows=3 width=52)
      ->  Merge Join  (cost=59.13..63.43 rows=33 width=44)
          ->  Sort  (cost=22.67..22.67 rows=10 width=28)
               ->  Seq Scan on contacts m  (cost=0.00..22.50 rows=10 width=28)
          ->  Sort  (cost=36.47..36.47 rows=333 width=16)
               ->  Seq Scan on buildings b  (cost=0.00..22.50 rows=333 width=16)
      ->  Sort  (cost=22.67..22.67 rows=10 width=8)
          ->  Seq Scan on contracts c  (cost=0.00..22.50 rows=10 width=8)
  ->  Index Scan using executives_pkey on executives e  (cost=0.00..8.14 rows=10 width=16)
--------------------------------------------------------------------------------
Merge Join  (cost=174.38..247.30 rows=333 width=76)
  ->  Index Scan using executives_pkey on executives e  (cost=0.00..60.00 rows=1000 width=16)
  ->  Sort  (cost=174.38..174.38 rows=33 width=60)
      ->  Merge Join  (cost=167.58..173.53 rows=33 width=60)
          ->  Merge Join  (cost=59.13..63.43 rows=33 width=44)
              ->  Sort  (cost=22.67..22.67 rows=10 width=28)
                  ->  Seq Scan on contacts m  (cost=0.00..22.50 rows=10 width=28)
              ->  Sort  (cost=36.47..36.47 rows=333 width=16)
                  ->  Seq Scan on buildings b  (cost=0.00..22.50 rows=333 width=16)
          ->  Sort  (cost=108.44..108.44 rows=100 width=16)
              ->  Merge Join  (cost=92.50..105.12 rows=100 width=16)
                  ->  Sort  (cost=69.83..69.83 rows=1000 width=12)
                      ->  Seq Scan on contracts c  (cost=0.00..20.00 rows=1000 width=12)
                  ->  Sort  (cost=22.67..22.67 rows=10 width=4)
                      ->  Seq Scan on l_portal_statuses l (cost=0.00..22.50 rows=10 width=4)
--snip!--

Hopefully that's not too ugly.

TIA

-- Dave





Message-ID: <Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net>
Permalink:  ../Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: dave@slickness.org
  Subject: Re: Please don't kill me!
  In-Reply-To: <Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net>
  References: <Pine.LNX.4.31.0101241131360.23483-100000@bubbles.electricutopia.net>

* 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