Thread: 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

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

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.

2009/7/29 A. Kretschmer <andreas.kretschmer@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

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