explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 36,876.828 40,680.288 ↓ 0.0 0 1

Delete on transfer_perturbations tp (cost=1,300,648.01..1,789,711.38 rows=1,751,405 width=42) (actual time=40,680.288..40,680.288 rows=0 loops=1)

2. 388.857 3,803.460 ↑ 10.2 172,260 1

Hash Anti Join (cost=1,300,648.01..1,789,711.38 rows=1,751,405 width=42) (actual time=3,289.426..3,803.460 rows=172,260 loops=1)

  • Hash Cond: (tp.printing_id = printings.id)
3. 76.226 217.604 ↑ 49.1 172,260 1

Nested Loop (cost=66,773.99..339,721.45 rows=8,451,988 width=40) (actual time=24.232..217.604 rows=172,260 loops=1)

4. 4.246 24.414 ↑ 9.2 324 1

HashAggregate (cost=66,773.43..66,803.12 rows=2,969 width=34) (actual time=24.222..24.414 rows=324 loops=1)

  • Group Key: ip.id
5. 6.042 20.168 ↓ 4.4 12,960 1

Nested Loop Left Join (cost=1.96..66,766.00 rows=2,969 width=34) (actual time=0.171..20.168 rows=12,960 loops=1)

  • Filter: ((ippc.id IS NULL) OR (ippc.mutable IS TRUE))
6. 0.195 7.970 ↑ 2.1 324 1

Nested Loop (cost=1.54..60,211.54 rows=680 width=32) (actual time=0.162..7.970 rows=324 loops=1)

7. 0.045 0.431 ↑ 1.0 54 1

Nested Loop Left Join (cost=0.98..703.79 rows=54 width=26) (actual time=0.017..0.431 rows=54 loops=1)

8. 0.045 0.278 ↑ 1.0 54 1

Nested Loop (cost=0.70..685.62 rows=54 width=20) (actual time=0.012..0.278 rows=54 loops=1)

9. 0.125 0.125 ↑ 1.0 54 1

Index Scan using plates_pkey on plates ipl (cost=0.42..296.74 rows=54 width=14) (actual time=0.006..0.125 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 Scan using experiments_pkey on experiments ie (cost=0.29..7.19 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=54)

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

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

  • Index Cond: (experiment_id = ie.id)
12. 7.344 7.344 ↑ 8.8 6 54

Index Scan using printings_plate_id_idx on printings ip (cost=0.56..1,101.47 rows=53 width=14) (actual time=0.129..0.136 rows=6 loops=54)

  • Index Cond: (plate_id = ipl.id)
  • Filter: (printed_at IS NULL)
13. 6.156 6.156 ↓ 2.9 40 324

Index Scan using ppc_unique_prescription_and_ordinal on printing_prescription_conditions ippc (cost=0.41..9.50 rows=14 width=15) (actual time=0.002..0.019 rows=40 loops=324)

  • Index Cond: (printing_prescription_id = ipp.id)
14. 116.964 116.964 ↑ 5.4 532 324

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations tp (cost=0.56..63.45 rows=2,847 width=10) (actual time=0.006..0.361 rows=532 loops=324)

  • Index Cond: (printing_id = ip.id)
15. 51.930 3,196.999 ↑ 30.0 140,867 1

Hash (cost=1,160,317.31..1,160,317.31 rows=4,231,577 width=10) (actual time=3,196.999..3,196.999 rows=140,867 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 1,082kB
16. 3,145.069 3,145.069 ↑ 30.0 140,867 1

Seq Scan on printings (cost=0.00..1,160,317.31 rows=4,231,577 width=10) (actual time=0.008..3,145.069 rows=140,867 loops=1)

  • Filter: (printed_at IS NOT NULL)
  • Rows Removed by Filter: 38,472
Planning time : 1.075 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint plate_well_reagents_printing_reagent_id_fkey 2,015.383 ms 172260 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey 1,666.158 ms 172260 0.010 ms
check_printing_reagents_mutability_trg 10,396.000 ms 172260 0.060 ms
platelet_printings_reagents_updated_at: time=25647.340 calls=172260"Trigger trg_stash_experiment_version_printing_reagents 13,236.792 ms 172260 0.077 ms