Forum OpenACS Development: Re: Slow Query in Logger

Collapse
5: Re: Slow Query in Logger (response to 1)
Posted by Alex Kroman on
Here you go:

ibr=# explain analyze select submitter.first_names || ' ' || submitter.last_name as label,
ibr-# submitter.person_id as user_id
ibr-# from persons submitter,
ibr-# logger_entries le,
ibr-# acs_objects ao
ibr-# where ao.object_id = le.entry_id
ibr-# and submitter.person_id = ao.creation_user
ibr-# and exists (select 1
ibr(# from logger_project_pkg_map
ibr(# where project_id = le.project_id
ibr(# and package_id = '784')
ibr-# group by submitter.person_id, submitter.first_names, submitter.last_name
ibr-# order by submitter.first_names, submitter.last_name
ibr-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=111427.29..111427.46 rows=69 width=23) (actual time=7617.886..7617.902 rows=46 loops=1)
Sort Key: submitter.first_names, submitter.last_name
-> HashAggregate (cost=111424.84..111425.18 rows=69 width=23) (actual time=7617.251..7617.485 rows=46 loops=1)
-> Hash Join (cost=94996.07..111305.26 rows=15944 width=23) (actual time=1007.526..7483.693 rows=32715 loops=1)
Hash Cond: ("outer".creation_user = "inner".person_id)
-> Hash Join (cost=94994.21..111062.16 rows=16358 width=4) (actual time=1006.676..7352.151 rows=32715 loops=1)
Hash Cond: ("outer".object_id = "inner".entry_id)
-> Seq Scan on acs_objects ao (cost=0.00..8554.95 rows=319695 width=8) (actual time=0.081..931.071 rows=319695 loops=1)
-> Hash (cost=94897.31..94897.31 rows=16358 width=4) (actual time=1006.113..1006.113 rows=0 loops=1)
-> Seq Scan on logger_entries le (cost=0.00..94897.31 rows=16358 width=4) (actual time=0.808..911.293 rows=32715 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using logger_project_pkg_map_un on logger_project_pkg_map (cost=0.00..5.75 rows=2 width=0) (actual time=0.019..0.019 rows=1 loops=32715)
Index Cond: ((project_id = $0) AND (package_id = 784))
-> Hash (cost=1.69..1.69 rows=69 width=23) (actual time=0.771..0.771 rows=0 loops=1)
-> Seq Scan on persons submitter (cost=0.00..1.69 rows=69 width=23) (actual time=0.177..0.437 rows=69 loops=1)
Total runtime: 7620.962 ms
(17 rows)