explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PTt4

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 296,384.287 345,048.995 ↓ 0.0 0 1

Delete on transfer_perturbations pr (cost=3,337.80..3,478,899.40 rows=664 width=18) (actual time=345,048.995..345,048.995 rows=0 loops=1)

2. 7.032 48,664.708 ↓ 6.9 4,578 1

Nested Loop Anti Join (cost=3,337.80..3,478,899.40 rows=664 width=18) (actual time=1.581..48,664.708 rows=4,578 loops=1)

3. 16,400.692 48,231.922 ↓ 1.5 4,578 1

Hash Semi Join (cost=3,337.24..3,476,249.87 rows=3,016 width=16) (actual time=1.541..48,231.922 rows=4,578 loops=1)

  • Hash Cond: (pr.printing_id = printings_1.id)
4. 31,829.721 31,829.782 ↓ 3.6 73,757,177 1

Seq Scan on transfer_perturbations pr (cost=47.22..3,406,824.97 rows=20,320,070 width=10) (actual time=0.081..31,829.782 rows=73,757,177 loops=1)

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

SubPlan (for Seq Scan)

6. 0.001 0.061 ↓ 0.0 0 1

Nested Loop (cost=1.40..47.19 rows=10 width=4) (actual time=0.061..0.061 rows=0 loops=1)

7. 0.004 0.042 ↓ 3.0 3 1

Nested Loop (cost=0.98..37.61 rows=1 width=4) (actual time=0.021..0.042 rows=3 loops=1)

8. 0.006 0.029 ↑ 1.0 3 1

Nested Loop (cost=0.70..36.60 rows=3 width=8) (actual time=0.015..0.029 rows=3 loops=1)

9. 0.014 0.014 ↑ 1.0 3 1

Index Scan using plates_pkey on plates pl (cost=0.42..19.66 rows=3 width=4) (actual time=0.008..0.014 rows=3 loops=1)

  • Index Cond: (id = ANY ('{130177,130178,131688}'::integer[]))
10. 0.009 0.009 ↑ 1.0 1 3

Index Only Scan using experiments_pkey on experiments e (cost=0.29..5.64 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=3)

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

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

  • Index Cond: (experiment_id = e.id)
12. 0.018 0.018 ↓ 0.0 0 3

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.006..0.006 rows=0 loops=3)

  • Index Cond: (printing_prescription_id = pp.id)
  • Filter: (mutable IS FALSE)
  • Rows Removed by Filter: 8
13. 0.008 1.448 ↑ 89.8 9 1

Hash (cost=3,279.92..3,279.92 rows=808 width=10) (actual time=1.448..1.448 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 1.440 1.440 ↑ 89.8 9 1

Index Scan using printings_plate_id_idx on printings printings_1 (cost=0.56..3,279.92 rows=808 width=10) (actual time=0.178..1.440 rows=9 loops=1)

  • Index Cond: (plate_id = ANY ('{130177,130178,131688}'::integer[]))
15. 425.754 425.754 ↓ 0.0 0 4,578

Index Scan using printings_pkey on printings (cost=0.56..0.87 rows=1 width=10) (actual time=0.093..0.093 rows=0 loops=4,578)

  • Index Cond: (id = pr.printing_id)
  • Filter: (printed_at IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 1.437 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint plate_well_reagents_printing_reagent_id_fkey 140.660 ms 4578 0.031 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey 508,732.998 ms 4578 111.126 ms
check_printing_reagents_mutability_trg 539.274 ms 4578 0.118 ms
platelet_printings_reagents_updated_at 1,029.139 ms 4578 0.225 ms
trg_stash_experiment_version_printing_reagents 1,715.362 ms 4578 0.375 ms