launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

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.