Re: can this query be made to run faster?

Please send us an 'EXPLAIN ANALYZE' of the query.

Thanks

On Fri, 2002-11-15 at 15:51, Peter T. Brown wrote:
> 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;
>
>
>
>
> 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)
--
Rod Taylor <rbt(at)rbt(dot)ca>