Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DA2BA475AE5
	for <pgsql-sql@postgresql.org>; Fri, 15 Nov 2002 14:36:07 -0500 (EST)
Received: from mail.memeticsystems.com (flywheel.memeticsystems.com
	[64.85.80.85]) by postgresql.org (Postfix) with SMTP id 0D0114759BD
	for <pgsql-sql@postgresql.org>; Fri, 15 Nov 2002 14:36:07 -0500 (EST)
Received: (qmail 4063 invoked from network); 15 Nov 2002 19:36:07 -0000
Received: from unknown (HELO ?10.0.0.102?) (63.249.24.21)
	by 0 with SMTP; 15 Nov 2002 19:36:07 -0000
Subject: how can I improve the speed of this query
From: "Peter T. Brown" <peter@memeticsystems.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <web-1827592@davinci.ethosmedia.com>
References: <web-1827592@davinci.ethosmedia.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.8 
Date: 15 Nov 2002 12:33:29 -0800
Message-Id: <1037392411.2473.36.camel@localhost.localdomain>
Mime-Version: 1.0
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/233
X-Sequence-Number: 10375

Hi--

I have this rather long complex query that takes really long to complete
(please see below). It seems like I ought to improve the speed somehow.
I don't understand, for example, what the query planner is doing when it
says "Hash" and why this appears to take so long. And since I have a key
for Visitor.ID, I don't understand why its doing a sequential scan on
that table... 

Any advice would be greatly appreciated!


Thanks

Peter




EXPLAIN SELECT 
	"Visitor"."Created",
	"Visitor"."Updated",
	"Tidbit"."ID",
	"ProgramEvent"."ID",
	"Visitor"."Email",
	"Interest"."ID",
	"VisitorInternetDeviceAssoc"."ID",
	"Referral"."ID" 

FROM "VisitorExtra" 

LEFT OUTER JOIN Tidbit" ON 
	"VisitorExtra"."ID"="Tidbit"."VisitorID" 

LEFT OUTER JOIN "ProgramEvent" ON
	"VisitorExtra"."ID"="ProgramEvent"."VisitorID" 

LEFT OUTER JOIN "Interest" ON 
	"VisitorExtra"."ID"="Interest"."VisitorID" 

LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON
	"VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID" 

LEFT OUTER JOIN "Referral" ON
	"VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor" 

WHERE "VisitorExtra"."ID"="Visitor"."ID" AND 
	"VisitorExtra"."ID"= 325903;


NOTICE:  QUERY PLAN:

Hash Join  (cost=14584.37..59037.79 rows=57747 width=76)
  ->  Merge Join  (cost=0.00..36732.65 rows=57747 width=44)
        ->  Merge Join  (cost=0.00..29178.16 rows=10681 width=36)
              ->  Nested Loop  (cost=0.00..10505.74 rows=6674 width=28)
                    ->  Nested Loop  (cost=0.00..435.29 rows=177
			width=20)
                          ->  Nested Loop  (cost=0.00..15.70 rows=55
				width=12)
                                ->  Index Scan using VisitorExtra_pkey
				    on VisitorExtra  (cost=0.00..3.01 rows=1 				    width=4)
                                ->  Index Scan using 				   
Tidbit_VisitorID_key on Tidbit  				    (cost=0.00..12.67 rows=2
width=8)
                          ->  Index Scan using 			     	 			     
ProgramEvent_VisitorID_key on ProgramEvent  			      (cost=0.00..7.57
rows=2 width=8)
                    ->  Index Scan using Interest_VisitorID_key on
			Interest  (cost=0.00..56.66 rows=19 width=8)
              ->  Index Scan using VisitorInternetDeviceAssoc_Visi on
		  VisitorInternetDeviceAssoc  (cost=0.00..16402.90 rows=174887 		 
width=8)
        ->  Index Scan using Referral_FromVisitorID_key on Referral 
	    (cost=0.00..6323.41 rows=87806 width=8)
  ->  Hash  (cost=6061.79..6061.79 rows=317379 width=32)
        ->  Seq Scan on Visitor  (cost=0.00..6061.79 rows=317379 	   
width=32)