horrendous query challenge :-)
От | Fran Fabrizio |
---|---|
Тема | horrendous query challenge :-) |
Дата | |
Msg-id | 3CF5486A.4090301@mmrd.com обсуждение исходный текст |
Ответы |
Re: horrendous query challenge :-)
Re: horrendous query challenge :-) |
Список | pgsql-general |
Hello, I'm posting this sort of as a learning exercise to see how others approach a slow query and what steps they take to optimize it. I'm aware that this query is pretty hideous, but unfortunately it's all too typical of the type of thing our app is asked to do. It's a join of five tables (rather, 4 tables and a view). There are as of yet no indexes defined - that part of the process I want to learn - where to apply indexes most effectively. The following query takes several minutes to run. current_status_test is a view, definition at the end of this email. My coworker authored this query. He's a SQL novice, and I'm somewhere in the novice-intermediate range, so when his queries run slow, it falls to me to try to optimize them. Unfortunately, I'm not very good at this particular activity. :-) What steps would you all take to try to speed up this query? I'm interested to see peoples' various approaches. I'll try them all out and report execution times before and after. Thanks for your time! Thanks, Fran monitor=# explain select cs.entity_id,e.type,e.name,w.interface_label,cs.remote_ts,s.name,r.name from current_status_test cs, entity e, site s, region r,watch w where cs.entity_id in (select entity_id from entity where active=true) and cs.current = false and cs.msgtype=w.msg_type and e.entity_id = cs.entity_id and s.region_id = r.region_id and cs.site_id = s.site_id order by cs.remote_ts desc, r.name, s.name; NOTICE: QUERY PLAN: Sort (cost=100018792.67..100018792.67 rows=1 width=108) -> Merge Join (cost=100018792.56..100018792.66 rows=1 width=108) -> Sort (cost=100018791.39..100018791.39 rows=1 width=92) -> Nested Loop (cost=100018729.68..100018791.38 rows=1 width=92) -> Hash Join (cost=18729.68..18783.58 rows=1 width=72) -> Hash Join (cost=18728.30..18780.56 rows=28 width=56) -> Seq Scan on entity e (cost=0.00..16.74 rows=874 width=28) -> Hash (cost=18728.19..18728.19 rows=45 width=28) -> Hash Join (cost=3.58..18728.19 rows=45 width=28) -> Seq Scan on entity_watch_map e (cost=0.00..18720.79 rows=89 width=16) SubPlan -> Seq Scan on entity (cost=0.00..18.93 rows=451 width=4) -> Hash (cost=3.53..3.53 rows=22 width=12) -> Hash Join (cost=1.11..3.53 rows=22 width=12) -> Seq Scan on classifications class (cost=0.00..1.26 rows=26 width=8) -> Hash (cost=1.09..1.09 rows=9 width=4) -> Seq Scan on categories cat (cost=0.00..1.09 rows=9 width=4) -> Hash (cost=1.30..1.30 rows=30 width=16) -> Seq Scan on watch w (cost=0.00..1.30 rows=30 width=16) -> Seq Scan on site s (cost=0.00..5.52 rows=152 width=20) -> Sort (cost=1.17..1.17 rows=7 width=16) -> Seq Scan on region r (cost=0.00..1.07 rows=7 width=16) EXPLAIN monitor=# current_status_test definition: View definition: SELECT findsite(e.entity_id) AS site_id, e.entity_id, get_status(e.entity_id, e.watch_id) AS status, e.watch_id, e.msg_type AS msgtype, cat.name AS msgcat, 'Ok' AS message, now() AS local_ts, e.last_contact AS remote_ts, e.current FROM entity_watch_map e, classifications class, categories cat WHERE (((e.last_contact > "timestamp"((date(now()) - 180))) AND (e.msg_type = class.msg_type)) AND (class.category_id = cat.category_id));
В списке pgsql-general по дате отправления: