launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #23561
Re: [Merge] lp:~cjwatson/launchpad/fix-ordering into lp:launchpad
Diff comments:
>
> === modified file 'lib/lp/registry/model/distroseries.py'
> --- lib/lp/registry/model/distroseries.py 2019-02-13 14:39:18 +0000
> +++ lib/lp/registry/model/distroseries.py 2019-04-16 11:18:04 +0000
> @@ -1110,7 +1110,9 @@
> SourcePackagePublishingHistory.pocket == pocket,
> SourcePackagePublishingHistory.component == component,
> SourcePackagePublishingHistory.status ==
> - PackagePublishingStatus.PUBLISHED)
> + PackagePublishingStatus.PUBLISHED,
> + SourcePackagePublishingHistory.sourcepackagename ==
> + SourcePackageName.id).order_by(SourcePackageName.name)
I had a look on dogfood, picking bionic/RELEASE/universe(/amd64) on the grounds that it's a good-sized test case. Before this change:
launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT spph.* FROM sourcepackagepublishinghistory spph WHERE spph.archive = 1 AND spph.distroseries = 307 AND spph.pocket = 0 AND spph.component = 3 AND spph.status = 2; QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on sourcepackagepublishinghistory spph (cost=204.46..23889.15 rows=6852 width=129) (actual time=4.354..19.536 rows=26217 loops=1)
Recheck Cond: ((archive = 1) AND (distroseries = 307) AND (component = 3) AND (status = ANY ('{1,2}'::integer[])))
Filter: ((pocket = 0) AND (status = 2))
Rows Removed by Filter: 315
Heap Blocks: exact=6257
Buffers: shared hit=6361
-> Bitmap Index Scan on sourcepackagepublishinghistory__archive__distroseries__componen (cost=0.00..202.74 rows=7225 width=0) (actual time=3.126..3.126 rows=26532 loops=1)
Index Cond: ((archive = 1) AND (distroseries = 307) AND (component = 3))
Buffers: shared hit=104
Planning time: 0.268 ms
Execution time: 21.017 ms
(11 rows)
launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT bpph.* FROM binarypackagepublishinghistory bpph, distroarchseries das WHERE das.distroseries = 307 AND das.architecturetag = 'amd64' AND bpph.archive = 1 AND bpph.distroarchseries = das.id AND bpph.pocket = 0 AND bpph.component = 3 AND bpph.status = 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=434.18..58179.31 rows=2612 width=118) (actual time=14.935..81.745 rows=68463 loops=1)
Buffers: shared hit=18066
-> Seq Scan on distroarchseries das (cost=0.00..4.79 rows=1 width=4) (actual time=0.016..0.032 rows=1 loops=1)
Filter: ((distroseries = 307) AND (architecturetag = 'amd64'::text))
Rows Removed by Filter: 185
Buffers: shared hit=2
-> Bitmap Heap Scan on binarypackagepublishinghistory bpph (cost=434.18..58148.12 rows=2640 width=118) (actual time=14.915..63.144 rows=68463 loops=1)
Recheck Cond: ((archive = 1) AND (distroarchseries = das.id) AND (status = 2))
Filter: ((pocket = 0) AND (component = 3))
Rows Removed by Filter: 27136
Heap Blocks: exact=17694
Buffers: shared hit=18064
-> Bitmap Index Scan on binarypackagepublishinghistory__archive__distroarchseries__stat (cost=0.00..433.52 rows=15436 width=0) (actual time=11.432..11.432 rows=95599 loops=1)
Index Cond: ((archive = 1) AND (distroarchseries = das.id) AND (status = 2))
Buffers: shared hit=370
Planning time: 0.487 ms
Execution time: 85.565 ms
(17 rows)
After this change:
launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT spph.* FROM sourcepackagepublishinghistory spph, sourcepackagename spn WHERE spph.archive = 1 AND spph.distroseries = 307 AND spph.pocket = 0 AND spph.component = 3 AND spph.status = 2 AND spph.sourcepackagename = spn.id ORDER BY spn.name;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=27373.26..28039.48 rows=5710 width=144) (actual time=62.870..87.535 rows=26217 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=85114
-> Sort (cost=26373.24..26380.38 rows=2855 width=144) (actual time=58.580..59.476 rows=8739 loops=3)
Sort Key: spn.name
Sort Method: quicksort Memory: 1734kB
Buffers: shared hit=85114
-> Nested Loop (cost=204.75..26209.37 rows=2855 width=144) (actual time=4.564..43.886 rows=8739 loops=3)
Buffers: shared hit=85098
-> Parallel Bitmap Heap Scan on sourcepackagepublishinghistory spph (cost=204.46..23794.32 rows=2855 width=129) (actual time=4.532..15.251 rows=8739 loops=3)
Recheck Cond: ((archive = 1) AND (distroseries = 307) AND (component = 3) AND (status = ANY ('{1,2}'::integer[])))
Filter: ((pocket = 0) AND (status = 2))
Rows Removed by Filter: 105
Heap Blocks: exact=2530
Buffers: shared hit=6361
-> Bitmap Index Scan on sourcepackagepublishinghistory__archive__distroseries__componen (cost=0.00..202.74 rows=7225 width=0) (actual time=6.233..6.233 rows=26532 loops=1)
Index Cond: ((archive = 1) AND (distroseries = 307) AND (component = 3))
Buffers: shared hit=104
-> Index Scan using sourcepackagename_pkey on sourcepackagename spn (cost=0.29..0.85 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=26217)
Index Cond: (id = spph.sourcepackagename)
Buffers: shared hit=78737
Planning time: 0.609 ms
Execution time: 90.405 ms
(24 rows)
launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT bpph.* FROM binarypackagepublishinghistory bpph, distroarchseries das, binarypackagename bpn WHERE das.distroseries = 307 AND das.architecturetag = 'amd64' AND bpph.archive = 1 AND bpph.distroarchseries = das.id AND bpph.pocket = 0 AND bpph.component = 3 AND bpph.status = 2 AND bpph.binarypackagename = bpn.id ORDER BY bpn.name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=77529.14..77535.67 rows=2612 width=149) (actual time=504.820..524.463 rows=68463 loops=1)
Sort Key: bpn.name
Sort Method: external merge Disk: 6464kB
Buffers: shared hit=292084, temp read=808 written=809
-> Nested Loop (cost=434.60..77380.90 rows=2612 width=149) (actual time=13.849..336.809 rows=68463 loops=1)
Buffers: shared hit=292084
-> Nested Loop (cost=434.18..58179.31 rows=2612 width=118) (actual time=13.836..94.612 rows=68463 loops=1)
Buffers: shared hit=18066
-> Seq Scan on distroarchseries das (cost=0.00..4.79 rows=1 width=4) (actual time=0.016..0.032 rows=1 loops=1)
Filter: ((distroseries = 307) AND (architecturetag = 'amd64'::text))
Rows Removed by Filter: 185
Buffers: shared hit=2
-> Bitmap Heap Scan on binarypackagepublishinghistory bpph (cost=434.18..58148.12 rows=2640 width=118) (actual time=13.816..73.712 rows=68463 loops=1)
Recheck Cond: ((archive = 1) AND (distroarchseries = das.id) AND (status = 2))
Filter: ((pocket = 0) AND (component = 3))
Rows Removed by Filter: 27136
Heap Blocks: exact=17694
Buffers: shared hit=18064
-> Bitmap Index Scan on binarypackagepublishinghistory__archive__distroarchseries__stat (cost=0.00..433.52 rows=15436 width=0) (actual time=10.492..10.492 rows=95599 loops=1)
Index Cond: ((archive = 1) AND (distroarchseries = das.id) AND (status = 2))
Buffers: shared hit=370
-> Index Scan using binarypackagename_pkey on binarypackagename bpn (cost=0.42..7.35 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=68463)
Index Cond: (id = bpph.binarypackagename)
Buffers: shared hit=274018
Planning time: 0.811 ms
Execution time: 541.687 ms
(26 rows)
So in both cases this seems to be as I'd expected: the core of the plan remains the same, but an index scan of the xPN is appended and the combination is wrapped in a sort. The binary version slows down more than the source version, but I think that's just the result of having to combine and sort ~68000 rows rather than ~26000 and tipping over the threshold where PostgreSQL picks an external merge sort; production has a higher work_mem than dogfood so it should be faster than that.
>
> def eager_load(spphs):
> # Preload everything which will be used by archivepublisher's
--
https://code.launchpad.net/~cjwatson/launchpad/fix-ordering/+merge/366102
Your team Launchpad code reviewers is subscribed to branch lp:launchpad.
References