[SQL] making queries more effecient

[Peter T. Brown]

Hi. I have this query that I have been trying to reduce to a single
statement, but haven't figured out how. Am I missing something?

CREATE TEMP TABLE temp20561149207391 AS SELECT DISTINCT ON ("VisitorID")
"VisitorID","Type" FROM "ProgramEvent" WHERE "ProgramID" = 10 ORDER BY
"VisitorID","Created" DESC;INSERT INTO "VisitorPointer839" ("VisitorID")
SELECT temp20561149207391."VisitorID" FROM temp20561149207391  WHERE
temp20561149207391."Type" = 0


Thanks


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] how can I improve the speed of this query

[Peter T. Brown]

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)



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org