Here's the query plan with this change, which looks much healthier:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1763277.47..1763503.38 rows=76 width=3205) (actual
time=246.653..246.774 rows=76 loops=1)
Buffers: shared hit=30805 read=11178 dirtied=36
CTE relevantperson
-> Nested Loop (cost=0.86..1757.02 rows=1036 width=4) (actual
time=0.084..16.539 rows=1026 loops=1)
Buffers: shared hit=3440 read=367
-> Index Only Scan using teamparticipation_team_key on
teamparticipation (cost=0.43..54.82 rows=1036 width=4) (actual
time=0.060..1.990 rows=1026 loops=1)
Index Cond: (team = 2962068)
Filter: (person <> 2962068)
Rows Removed by Filter: 1
Heap Fetches: 65
Buffers: shared hit=128 read=67
-> Index Only Scan using person_pkey on person person_1
(cost=0.43..1.64 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1026)
Index Cond: (id = teamparticipation.person)
Heap Fetches: 34
Buffers: shared hit=3312 read=300
-> Result (cost=1760851.64..1763931.15 rows=1036 width=3205) (actual
time=246.325..246.748 rows=301 loops=1)
Buffers: shared hit=30805 read=11178 dirtied=36
-> Sort (cost=1760851.64..1760854.23 rows=1036 width=3204) (actual
time=222.665..222.717 rows=301 loops=1)
Sort Key: (person_sort_key(person.displayname, person.name))
Sort Method: top-N heapsort Memory: 534kB
Buffers: shared hit=30804 read=10108 dirtied=36
-> Nested Loop Left Join (cost=1227.80..1760803.81 rows=1036
width=3204) (actual time=13.180..218.892 rows=1026 loops=1)
Buffers: shared hit=30801 read=10108 dirtied=36
-> Nested Loop Left Join (cost=1227.23..1758892.09
rows=1036 width=3065) (actual time=0.799..164.552 rows=1026 loops=1)
Buffers: shared hit=30022 read=9607 dirtied=34
-> Nested Loop Left Join (cost=1226.66..1757239.37
rows=1036 width=2958) (actual time=0.744..148.998 rows=1026 loops=1)
Buffers: shared hit=29613 read=9131 dirtied=29
-> Nested Loop Left Join
(cost=1226.23..1756751.90 rows=1036 width=2852) (actual time=0.726..123.061
rows=1026 loops=1)
Buffers: shared hit=27159 read=7492
dirtied=29
-> Nested Loop Left Join
(cost=1225.80..1756279.84 rows=1036 width=2811) (actual time=0.690..101.350
rows=1026 loops=1)
Buffers: shared hit=24678
read=5868 dirtied=29
-> Nested Loop Left Join
(cost=1.27..3841.73 rows=1036 width=1181) (actual time=0.165..50.325 rows=1026
loops=1)
Buffers: shared hit=12377
read=2450 dirtied=29
-> Nested Loop Left Join
(cost=0.85..3387.95 rows=1036 width=1120) (actual time=0.140..41.158 rows=1026
loops=1)
Buffers: shared
hit=9484 read=1859 dirtied=29
-> Nested Loop
(cost=0.43..2936.12 rows=1036 width=1108) (actual time=0.121..29.892 rows=1026
loops=1)
Buffers: shared
hit=6792 read=1121
-> CTE Scan on
relevantperson (cost=0.00..20.72 rows=1036 width=4) (actual time=0.099..17.303
rows=1026 loops=1)
Buffers:
shared hit=3440 read=367
-> Index Scan
using person_pkey on person (cost=0.43..2.81 rows=1 width=1108) (actual
time=0.011..0.011 rows=1 loops=1026)
Index
Cond: (id = relevantperson.id)
Buffers:
shared hit=3352 read=754
-> Index Scan using
karmatotalcache_person_key on karmatotalcache (cost=0.42..0.44 rows=1
width=12) (actual time=0.010..0.010 rows=0 loops=1026)
Index Cond:
(person = person.id)
Buffers: shared
hit=2692 read=738 dirtied=29
-> Index Scan using
personlocation_person_key on personlocation (cost=0.42..0.44 rows=1 width=61)
(actual time=0.008..0.008 rows=0 loops=1026)
Index Cond: (person =
person.id)
Buffers: shared
hit=2893 read=591
-> Bitmap Heap Scan on archive
(cost=1224.53..1691.53 rows=1 width=1630) (actual time=0.047..0.047 rows=0
loops=1026)
Recheck Cond: ((id IS NULL)
OR (id = (SubPlan 4)))
Filter: ((id IS NULL) OR
((owner = person.id) AND (id = (SubPlan 4))))
Heap Blocks: exact=355
Buffers: shared hit=12301
read=3418
-> BitmapOr
(cost=1224.53..1224.53 rows=1 width=0) (actual time=0.042..0.042 rows=0
loops=1026)
Buffers: shared
hit=7344 read=3418
-> Bitmap Index Scan
on archive_pkey (cost=0.00..1.64 rows=1 width=0) (actual time=0.002..0.002
rows=0 loops=1026)
Index Cond: (id
IS NULL)
Buffers: shared
hit=3078
SubPlan 4
-> Aggregate
(cost=465.78..465.79 rows=1 width=4) (actual time=0.026..0.026 rows=1
loops=1381)
Buffers:
shared hit=8727 read=2491
->
Index Scan using archive__owner__idx on archive archive_1 (cost=0.43..464.22
rows=623 width=4) (actual time=0.007..0.024 rows=5 loops=1381)
Index Cond: (owner = person.id)
Filter: (purpose = 2)
Rows Removed by Filter: 0
Buffers: shared hit=8727 read=2491
-> Index Scan using
emailaddress__person__key on emailaddress (cost=0.43..0.46 rows=1 width=41)
(actual time=0.017..0.017 rows=1 loops=1026)
Index Cond: (person = person.id)
Buffers: shared hit=2496 read=1609
-> Index Scan using account_pkey on account
(cost=0.43..0.47 rows=1 width=106) (actual time=0.016..0.016 rows=1 loops=1026)
Index Cond: (person.account = id)
Filter: (status = 20)
Rows Removed by Filter: 0
Buffers: shared hit=2572 read=1521
-> Index Scan using libraryfilealias_pkey on
libraryfilealias logoalias (cost=0.57..1.60 rows=1 width=107) (actual
time=0.005..0.005 rows=0 loops=1026)
Index Cond: (person.logo = id)
Buffers: shared hit=441 read=444
-> Index Scan using libraryfilealias_pkey on
libraryfilealias mugshotalias (cost=0.57..1.60 rows=1 width=107) (actual
time=0.006..0.006 rows=0 loops=1026)
Index Cond: (person.mugshot = id)
Buffers: shared hit=809 read=471
SubPlan 2
-> Index Scan using signedcodeofconduct_owner_idx on
signedcodeofconduct (cost=0.29..2.71 rows=1 width=0) (never executed)
Index Cond: (owner = person.id)
Filter: (active AND (datecreated >= '2005-04-12
00:00:00'::timestamp without time zone))
SubPlan 3
-> Seq Scan on signedcodeofconduct signedcodeofconduct_1
(cost=0.00..1727.11 rows=47012 width=4) (actual time=0.015..14.598 rows=43597
loops=1)
Filter: (active AND (datecreated >= '2005-04-12
00:00:00'::timestamp without time zone))
Rows Removed by Filter: 5076
Buffers: shared hit=13 read=1058
Planning time: 18.029 ms
Execution time: 194.158 ms
(94 rows)
--
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/400391
Your team Launchpad code reviewers is requested to review the proposed merge of
~cjwatson/launchpad:getPrecachedPersons-performance into launchpad:master.
_______________________________________________
Mailing list: https://launchpad.net/~launchpad-reviewers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~launchpad-reviewers
More help : https://help.launchpad.net/ListHelp
