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.
_______________________________________________
Mailing list: https://launchpad.net/~launchpad-reviewers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~launchpad-reviewers
More help : https://help.launchpad.net/ListHelp
