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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
