Explaining explain ...

Explaining explain ...

Post by Steve Bret » Sat, 19 Jan 2002 18:59:04

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

this is the query:

            contact_types.description as contact_type,
            people_titles.description as title
            INNER JOIN contact_details ON org_contacts.org_contact_id =
            INNER JOIN contact_types ON contact_details.contact_type =
            LEFT OUTER JOIN people ON contact_details.person_id =
            LEFT OUTER JOIN people_titles ON people.title =
            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
                          ->  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 ?



1. explain EXPLAIN?

  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

2. REVIEW: Rhinoskin m505/500 molded aluminun hardcase vs. Scribble m5 Chameleon m505 hardcase

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

4. WindowsMe Icon glitch

5. negatives explained

6. MUME 0.6 Available

7. PLEASE explain color profiling to me

8. Vuescan RGB exposure: explain please.

9. Dynamic Range-please explain?

10. Please Explain 36 bit color

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

12. Xbox vs. PS2: Performance Gap Explained