Comment 1 for bug 1500973

I think we incur a large cost here from rewriting many rows unnecessarily:

launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) UPDATE BinaryPackageBuild SET virtualized=NOT Processor.supports_nonvirtualized FROM Processor WHERE BinaryPackageBuild.archive=63108 AND BinaryPackageBuild.status=0 AND BinaryPackageBuild.processor=Processor.id;
                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on binarypackagebuild (cost=49.47..5659.90 rows=1528 width=108) (actual time=1667.053..1667.053 rows=0 loops=1) Buffers: shared hit=846845 read=1607 dirtied=4576atus=0 AND BinaryPackageBuil -> Hash Join (cost=49.47..5659.90 rows=1528 width=108) (actual time=27.692..74.754 rows=19640 loops=1)
         Hash Cond: (binarypackagebuild.processor = processor.id)
         Buffers: shared hit=8647
         -> Bitmap Heap Scan on binarypackagebuild (cost=48.22..5637.65 rows=1528 width=101) (actual time=27.659..48.549 rows=19640 loops=1)
               Recheck Cond: ((archive = 63108) AND (status = 0))
               Buffers: shared hit=8646
               -> Bitmap Index Scan on binarypackagebuild__archive__status__date_finished__id__idx (cost=0.00..47.84 rows=1528 width=0) (actual time=13.563..13.563 rows=58032 loops=1)
                     Index Cond: ((archive = 63108) AND (status = 0))
                     Buffers: shared hit=388
         -> Hash (cost=1.11..1.11 rows=11 width=11) (actual time=0.017..0.017 rows=11 loops=1)
               Buckets: 1024 Batches: 1 Memory Usage: 1kB
               Buffers: shared hit=1
               -> Seq Scan on processor (cost=0.00..1.11 rows=11 width=11) (actual time=0.006..0.010 rows=11 loops=1)
                     Buffers: shared hit=1
 Trigger for constraint binarypackagebuild_archive_fkey: time=357.263 calls=19640
 Trigger for constraint binarypackagebuild__distro_arch_series__fk: time=346.675 calls=19640
 Trigger for constraint binarypackagebuild_distro_series_fkey: time=347.908 calls=19640
 Trigger for constraint binarypackagebuild_distribution_fkey: time=376.901 calls=19640
 Trigger for constraint binarypackagebuild_build_farm_job_fkey: time=375.608 calls=19640
 Trigger for constraint binarypackagebuild_processor_fkey: time=298.695 calls=19640
 Trigger for constraint binarypackagebuild_source_package_name_fkey: time=360.448 calls=19640
 Trigger for constraint binarypackagebuild__source_package_release__fk: time=382.187 calls=19640
 Total runtime: 4545.115 ms
(25 rows)

launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) UPDATE BinaryPackageBuild SET virtualized=NOT Processor.supports_nonvirtualized FROM Processor WHERE BinaryPackageBuild.archive=63108 AND BinaryPackageBuild.status=0 AND BinaryPackageBuild.processor=Processor.id AND BinaryPackageBuild.virtualized=Processor.supports_nonvirtualized;
                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on binarypackagebuild (cost=49.53..5669.96 rows=844 width=108) (actual time=53.515..53.515 rows=0 loops=1)
   Buffers: shared hit=9006
   -> Hash Join (cost=49.53..5669.96 rows=844 width=108) (actual time=53.513..53.513 rows=0 loops=1)
         Hash Cond: ((binarypackagebuild.processor = processor.id) AND (binarypackagebuild.virtualized = processor.supports_nonvirtualized))
         Buffers: shared hit=9006
         -> Bitmap Heap Scan on binarypackagebuild (cost=48.25..5648.76 rows=1531 width=102) (actual time=32.248..48.683 rows=19640 loops=1)
               Recheck Cond: ((archive = 63108) AND (status = 0))
               Buffers: shared hit=9005
               -> Bitmap Index Scan on binarypackagebuild__archive__status__date_finished__id__idx (cost=0.00..47.87 rows=1531 width=0) (actual time=17.921..17.921 rows=77623 loops=1)
                     Index Cond: ((archive = 63108) AND (status = 0))
                     Buffers: shared hit=486
         -> Hash (cost=1.11..1.11 rows=11 width=11) (actual time=0.017..0.017 rows=11 loops=1)
               Buckets: 1024 Batches: 1 Memory Usage: 1kB
               Buffers: shared hit=1
               -> Seq Scan on processor (cost=0.00..1.11 rows=11 width=11) (actual time=0.006..0.010 rows=11 loops=1)
                     Buffers: shared hit=1
 Total runtime: 53.619 ms
(17 rows)