explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yWZ2 : Optimization for: Optimization for: Optimization for: Optimization for: plan #PTt4; plan #1SCb; plan #3jBG; plan #MlVt

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 260,549.670 339,670.816 ↓ 0.0 0 1

Delete on transfer_perturbations pr (cost=52,839.83..3,579,762.03 rows=10,249 width=18) (actual time=339,670.816..339,670.816 rows=0 loops=1)

2. 254.128 79,121.146 ↓ 16.8 172,260 1

Nested Loop Anti Join (cost=52,839.83..3,579,762.03 rows=10,249 width=18) (actual time=15.266..79,121.146 rows=172,260 loops=1)

3. 16,363.939 45,620.838 ↓ 3.5 172,260 1

Hash Join (cost=52,839.27..3,536,311.88 rows=49,460 width=16) (actual time=15.048..45,620.838 rows=172,260 loops=1)

  • Hash Cond: (pr.printing_id = printings_1.id)
4. 29,241.944 29,242.874 ↓ 3.6 73,993,067 1

Seq Scan on transfer_perturbations pr (cost=707.19..3,407,484.94 rows=20,320,070 width=10) (actual time=0.953..29,242.874 rows=73,993,067 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
5.          

SubPlan (for Seq Scan)

6. 0.027 0.930 ↓ 0.0 0 1

Nested Loop (cost=1.40..706.74 rows=181 width=4) (actual time=0.930..0.930 rows=0 loops=1)

7. 0.024 0.363 ↓ 2.5 54 1

Nested Loop (cost=0.98..495.79 rows=22 width=4) (actual time=0.021..0.363 rows=54 loops=1)

8. 0.041 0.231 ↑ 1.0 54 1

Nested Loop (cost=0.70..477.62 rows=54 width=8) (actual time=0.016..0.231 rows=54 loops=1)

9. 0.082 0.082 ↑ 1.0 54 1

Index Scan using plates_pkey on plates pl (cost=0.42..296.74 rows=54 width=4) (actual time=0.004..0.082 rows=54 loops=1)

  • Index Cond: (id = ANY ('{131849,131837,131827,131852,131817,131858,131838,131833,131834,131842,131867,131859,131818,131828,131855,131853,131832,131868,131850,131860,131829,131869,131857,131824,131845,131825,131854,131861,131835,131823,131836,131819,131843,131820,131848,131862,131816,131864,131865,131826,131863,131841,131821,131844,131856,131839,131846,131822,131830,131840,131847,131831,131851,131866}'::integer[]))
10. 0.108 0.108 ↑ 1.0 1 54

Index Only Scan using experiments_pkey on experiments e (cost=0.29..3.34 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=54)

  • Index Cond: (id = pl.experiment_id)
  • Heap Fetches: 54
11. 0.108 0.108 ↑ 1.0 1 54

Index Scan using printing_prescriptions_experiment_id_key on printing_prescriptions pp (cost=0.28..0.33 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=54)

  • Index Cond: (experiment_id = e.id)
12. 0.540 0.540 ↓ 0.0 0 54

Index Scan using ppc_unique_prescription_and_ordinal on printing_prescription_conditions ppc (cost=0.41..9.50 rows=9 width=8) (actual time=0.010..0.010 rows=0 loops=54)

  • Index Cond: (printing_prescription_id = pp.id)
  • Filter: (mutable IS FALSE)
  • Rows Removed by Filter: 40
13. 0.105 14.025 ↑ 39.8 324 1

Hash (cost=51,970.79..51,970.79 rows=12,903 width=10) (actual time=14.025..14.025 rows=324 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 142kB
14. 3.916 13.920 ↑ 39.8 324 1

Bitmap Heap Scan on printings printings_1 (cost=3,798.13..51,970.79 rows=12,903 width=10) (actual time=10.112..13.920 rows=324 loops=1)

  • Recheck Cond: (plate_id = ANY ('{131849,131837,131827,131852,131817,131858,131838,131833,131834,131842,131867,131859,131818,131828,131855,131853,131832,131868,131850,131860,131829,131869,131857,131824,131845,131825,131854,131861,131835,131823,131836,131819,131843,131820,131848,131862,131816,131864,131865,131826,131863,131841,131821,131844,131856,131839,131846,131822,131830,131840,131847,131831,131851,131866}'::integer[]))
  • Heap Blocks: exact=934
15. 10.004 10.004 ↓ 6.9 88,915 1

Bitmap Index Scan on printings_plate_id_idx (cost=0.00..3,794.90 rows=12,903 width=0) (actual time=10.004..10.004 rows=88,915 loops=1)

  • Index Cond: (plate_id = ANY ('{131849,131837,131827,131852,131817,131858,131838,131833,131834,131842,131867,131859,131818,131828,131855,131853,131832,131868,131850,131860,131829,131869,131857,131824,131845,131825,131854,131861,131835,131823,131836,131819,131843,131820,131848,131862,131816,131864,131865,131826,131863,131841,131821,131844,131856,131839,131846,131822,131830,131840,131847,131831,131851,131866}'::integer[]))
16. 33,246.180 33,246.180 ↓ 0.0 0 172,260

Index Scan using printings_pkey on printings (cost=0.56..0.87 rows=1 width=10) (actual time=0.193..0.193 rows=0 loops=172,260)

  • Index Cond: (id = pr.printing_id)
  • Filter: (printed_at IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 2.523 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint plate_well_reagents_printing_reagent_id_fkey 2,065.827 ms 172260 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey 1,710.007 ms 172260 0.010 ms
check_printing_reagents_mutability_trg 32,030.061 ms 172260 0.186 ms
platelet_printings_reagents_updated_at: time=75047.854 calls=172260"Trigger trg_stash_experiment_version_printing_reagents 61,459.799 ms 172260 0.357 ms