Message-ID: <008101bf21aa$af7b17e0$ea7c3eca@john.is.com.fj>
Reply-To: "John Henderson" <jrh@is.com.fj>
From: "John Henderson" <jrh@is.com.fj>
To: <pgsql-general@hub.org>
Subject: EXPLAIN
Date: Fri, 29 Oct 1999 13:12:28 +1200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi folks,
Where can i find some notes on how to interpret a query plan as generated by
EXPLAIN? This seems like a useful tool especially for a beginner like me but
I need some tips on using it :)
Here is an example:
isfiji=> explain select u.custid, u.username,
isfiji-> u.realname, u.level, u.address, u.contact, u.phone, u.fax,
u.circuit,
isfiji-> u.email, t.town,
isfiji-> p.profession, u.silent, u.startdate, a.area, u.notes, u.services,
isfiji-> u.active, u.passwd, u.suspended, u.disconnected,u.h_phone,
u.rec_date,
isfiji-> u.con_date, u.susp_date, u.disc_date from userdir u,
isfiji-> area a,
isfiji-> profession p, town t where u.towncode=t.town_id and
u.profcode=p.prof_id
isfiji-> and a.area_id=t.area_id and
isfiji-> upper(circuit)~~'1480495'
isfiji-> order by u.realname;
NOTICE:  QUERY PLAN:

Sort  (cost=2.63 size=0 width=0)
  ->  Nested Loop  (cost=2.63 size=2 width=260)
        ->  Nested Loop  (cost=1.53 size=1 width=246)
              ->  Nested Loop  (cost=0.00 size=1 width=220)
                    ->  Seq Scan on profession p  (cost=0.00 size=0
width=14)
                    ->  Seq Scan on userdir u  (cost=269.06 size=364
width=206)
              ->  Seq Scan on town t  (cost=1.53 size=16 width=26)
        ->  Seq Scan on area a  (cost=1.10 size=3 width=14)

EXPLAIN

Example questions:
How is total cost calculated? Is the cost of an inner Loop or sub query
added to or multiplied by the enclosing loop? What should I be looking out
for? etc. etc.

Thanks for your time,
John Henderson