horrendous query challenge :-)

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));