Re: can this query be made to run faster? - Mailing list pgsql-performance

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@rbt.ca>