Postgres Pro
Downloads
Home   >   mailing lists

Thread: can this query be made to run faster?

can this query be made to run faster?

From
"Peter T. Brown"
Date:
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)


--

Peter T. Brown
Director Of Technology
Memetic Systems, Inc.
"Translating Customer Data Into Marketing Action."
206.335.2927
http://www.memeticsystems.com/

Re: can this query be made to run faster?

From
Rod Taylor
Date:
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>