Hi,
I'm trying to optimise a query at the moment, I've added some new
indexes to stop seq scans, but I'm now trying to work out if I can
stop a join using external sort to speed up the query. I've included
an explain analyze below and would appreciate any pointers to gaps in
my understanding.
explain analyze SELECT p.usercompanyid, 'people' AS type, p.id,
(p.firstname::text || ' '::text) || p.surname::text AS name,
p.assigned_to, p.owner, p.organisation_id, phr.username, p.private
FROM people p
LEFT JOIN organisation_roles pr ON p.organisation_id =
pr.organisation_id AND pr.read
LEFT JOIN hasrole phr ON pr.roleid = phr.roleid;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=25870.55..31017.51 rows=229367 width=92)
(actual time=2884.501..5147.047 rows=354834 loops=1)
Merge Cond: (phr.roleid = pr.roleid)
-> Index Scan using hasrole_roleid_username on hasrole phr
(cost=0.00..537.29 rows=9246 width=27) (actual time=0.049..41.782
rows=9246 loops=1)
-> Materialize (cost=25870.49..27204.80 rows=106745 width=81)
(actual time=2884.413..3804.537 rows=354834 loops=1)
-> Sort (cost=25870.49..26137.35 rows=106745 width=81)
(actual time=2884.406..3099.732 rows=111878 loops=1)
Sort Key: pr.roleid
Sort Method: external merge Disk: 8928kB
-> Merge Left Join (cost=0.00..12027.25 rows=106745
width=81) (actual time=37.300..2519.719 rows=111878 loops=1)
Merge Cond: (p.organisation_id = pr.organisation_id)
-> Index Scan using person_company_id on people
p (cost=0.00..5286.23 rows=106745 width=73) (actual
time=37.216..1656.515 rows=106745 loops=1)
-> Index Scan using companyroles_org_search on
organisation_roles pr (cost=0.00..5410.60 rows=120342 width=16)
(actual time=0.073..268.645 rows=138299 loops=1)
Index Cond: (pr.read = true)
Filter: pr.read
Total runtime: 5588.105 ms
(14 rows)
Thanks
Jake