how can I improve the speed of this query
От | Peter T. Brown |
---|---|
Тема | how can I improve the speed of this query |
Дата | |
Msg-id | 1037392411.2473.36.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Updating a table column with ref integrity ("Josh Berkus" <josh@agliodbs.com>) |
Ответы |
Re: how can I improve the speed of this query
|
Список | pgsql-sql |
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) -> NestedLoop (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=19width=8) -> Index Scan using VisitorInternetDeviceAssoc_Visi on VisitorInternetDeviceAssoc (cost=0.00..16402.90rows=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)
В списке pgsql-sql по дате отправления: