Optimization via explicit JOINs

From: David Olbersen <dave(at)slickness(dot)org>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Optimization via explicit JOINs
Date: 2001-03-09 22:00:56
Message-ID: Pine.LNX.4.31.0103091054280.5324-100000@bubbles.electricutopia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-03-09 22:13:35 Re: Optimization via explicit JOINs
Previous Message Brent R. Matzelle 2001-03-09 21:54:38 Re: (blowfish in Postgres) Undefined symbol -- YAY!