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: Optimization via explicit JOINs
Date: Fri, 9 Mar 2001 14:00:56 -0800 (PST)
Message-ID: <Pine.LNX.4.31.0103091054280.5324-100000@bubbles.electricutopia.net> (raw)

Greetings,
  I've been toying aroudn with postgres 7.1beta5's ability to control the
  planner via explicitely JOINing tables. I then (just for giggles) compare the
  difference in the EXPLAIN results.

  I'm no super-mondo-DBA or anything, but in my two attempts so far, the numbers
  I get out of EXPLAIN have been about 1/2 as small.

  Below are two EXPLAIN results, am I correct in reading that one is indeed
  "twice as fast" as the other? I say twice as fast because the top-most cost in
  the first query is 58.62, but in the second one it's only 32.09. Am I reading
  this correctly?

-- First EXPLAIN --
Sort  (cost=58.62..58.62 rows=14 width=60)
  ->  Nested Loop  (cost=0.00..58.35 rows=14)
    ->  Nested Loop  (cost=0.00..29.99 rows=14)
      ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
      ->  Index Scan using songs_pkey on songs s (cost=0.00..2.01 rows=1)
    ->  Index Scan using artists_pkey on artists a (cost=0.00..2.01 rows=1)


-- Second EXPLAIN --
Sort  (cost=32.09..32.09 rows=1)
  ->  Nested Loop  (cost=0.00..32.08 rows=1)
    ->  Nested Loop  (cost=0.00..30.06 rows=1)
      ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
      ->  Index Scan using songs_pkey on songs s (cost=0.00..2.02 rows=1)
    ->  Index Scan using artists_pkey on artists a  (cost=0.00..2.01 rows=1)

-- Dave




view thread (10+ messages)  latest in thread

Message-ID: <Pine.LNX.4.31.0103091054280.5324-100000@bubbles.electricutopia.net>
Permalink:  ../Pine.LNX.4.31.0103091054280.5324-100000@bubbles.electricutopia.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.31.0103091054280.5324-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: Optimization via explicit JOINs
  In-Reply-To: <Pine.LNX.4.31.0103091054280.5324-100000@bubbles.electricutopia.net>
  References: <Pine.LNX.4.31.0103091054280.5324-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