From: Peter T. Brown <peter@memeticsystems.com>
To: pgsql-sql@postgresql.org
Subject: how can I improve the speed of this query
Date: 15 Nov 2002 12:33:29 -0800
Message-ID: <1037392411.2473.36.camel@localhost.localdomain> (raw)
In-Reply-To: <web-1827592@davinci.ethosmedia.com>
References: <web-1827592@davinci.ethosmedia.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter@memeticsystems.com
Subject: Re: how can I improve the speed of this query
In-Reply-To: <1037392411.2473.36.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox