explain EXPLAIN?

explain EXPLAIN?

Post by David Olbers » Sat, 10 Mar 2001 08:23:02



Hello,
  I'm looking for a better tutorial of how EXPLAIN works. I know Mr. Tom Lane
  wrote a "quick & dirty explanation" and that "plan-reading is an art that
  deserves a tutorial, and I haven't had time to write one". In which case I'd
  like to know if there's any other tutorials/resources.

  I think I get the jist of it (an index scan is better than a seq scan?) but
  I'd like to read more. Does anybody have any suggestions?

-- Dave

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

1. Explaining explain ...

hi I'm trying to work out why the following query takes a few seconds to
return results.

this is the query:

SELECT
            contact_details.data,
            people.surname,
            people.forename,
            contact_types.description as contact_type,
            people_titles.description as title
            FROM
            org_contacts
            INNER JOIN contact_details ON org_contacts.org_contact_id =
contact_details.org_contact_id
            INNER JOIN contact_types ON contact_details.contact_type =
contact_types.contact_type_id
            LEFT OUTER JOIN people ON contact_details.person_id =
people.person_id
            LEFT OUTER JOIN people_titles ON people.title =
people_titles.title_id
            WHERE
            org_contacts.org_id =  50;

this is what i get from explain

Hash Join  (cost=5384.13..137693.07 rows=3153185 width=92)
  ->  Merge Join  (cost=5383.04..11564.51 rows=3153185 width=76)
        ->  Index Scan using people_pkey on people  (cost=0.00..5206.05
rows=73759 width=32)
        ->  Sort  (cost=5383.04..5383.04 rows=4275 width=44)
              ->  Hash Join  (cost=111.70..5125.22 rows=4275 width=44)
                    ->  Merge Join  (cost=110.61..2681.20 rows=61071
width=28)
                          ->  Index Scan using
org_contact_id_contact_details_ on contact_details  (cost=0.00..2119.46 r
ows=35920 width=24)
                          ->  Sort  (cost=110.61..110.61 rows=170 width=4)
                                ->  Index Scan using org_id_org_contacts_key
on org_contacts  (cost=0.00..104.32 rows=17
0 width=4)
                    ->  Hash  (cost=1.07..1.07 rows=7 width=16)
                          ->  Seq Scan on contact_types  (cost=0.00..1.07
rows=7 width=16)
  ->  Hash  (cost=1.07..1.07 rows=7 width=16)
        ->  Seq Scan on people_titles  (cost=0.00..1.07 rows=7 width=16)

What i don't understand is the Hash join on the first row (rows 3153185 ?)
and the sort ... can anyone help ?

Steve

2. Upgrading from SBS 4.x to new Server should I buy upgrade version or complete

3. Any Website that will explain handshaking & Training signal of Fax (will explain the protocol monitor printout)

4. Minimal Firewall??

5. negatives explained

6. Looking to buy old DEC PDP computers and parts

7. PLEASE explain color profiling to me

8. Newbie question

9. Vuescan RGB exposure: explain please.

10. Dynamic Range-please explain?

11. Please Explain 36 bit color

12. Great site: Explains how to install USB scanner under Win95

13. Xbox vs. PS2: Performance Gap Explained