[GENERAL] Query optimisation and sorting on external merge
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query optimisation and sorting on external merge
In response to Jake Stride : 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 Increase work_mem to force sort in memory. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query optimisation and sorting on external merge
Hello, Le 29/07/09 13:46, Jake Stride a écrit : 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; [...] A first idea could be to explicitely join tables organisation_roles and hasrole before joining with table people. The two first tables are assumed to be of very small size compared to the (main) table people. Joining both them as a preliminary step would reduce the number of rows to join to the latter and thence make the table people scan faster. A second idea may be to move the clause pr.read into a subquery (sub-select) of table organisation_roles because this latter is the only table concerned with this clause. Thus, in spite of (hash- or index-based) scanning the whole table organisation_roles, a smaller part would be relevant. Combining these two ideas, a corresponding rewritten query would be as follows: 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 ( (SELECT pr2.roleid, pr2.organisation_id FROM organisation_roles pr2 WHERE pr2.read) pr LEFT JOIN hasrole phr ON pr.roleid = phr.roleid ) t ON p.organisation_id = t.organisation_id; Let you consider if the corresponding query plan looks better. Regards. -- nha / Lyon / France. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query optimisation and sorting on external merge
2009/7/29 A. Kretschmer andreas.kretsch...@schollglas.com: In response to Jake Stride : 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 Increase work_mem to force sort in memory. That stops the external storage thanks. I still think I need to optimise the query though as there are 3 other similar queries which are unioned together to form a view. Jake Andreas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query optimisation and sorting on external merge
Thanks I'll take a look into it - they query you provide seems to take longer in the query plan but I can see where you are coming from and it's good base to work from. Jake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
