Search Postgresql Archives

Re: Query optimisation and sorting on external merge

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux