Re: 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.