launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #26768
Re: [Merge] ~cjwatson/launchpad:getPrecachedPersons-performance into launchpad:master
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.
References
