explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,240.563 47,030.222 ↓ 0.0 0 1

Delete on transfer_perturbations pr (cost=2,089.60..3,471,385.51 rows=380 width=18) (actual time=47,030.222..47,030.222 rows=0 loops=1)

2. 8.792 45,789.659 ↓ 17.4 6,596 1

Nested Loop Anti Join (cost=2,089.60..3,471,385.51 rows=380 width=18) (actual time=10.338..45,789.659 rows=6,596 loops=1)

3. 16,339.139 45,602.775 ↓ 3.6 6,596 1

Hash Semi Join (cost=2,089.04..3,469,776.11 rows=1,832 width=16) (actual time=10.046..45,602.775 rows=6,596 loops=1)

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

Seq Scan on transfer_perturbations pr (cost=36.95..3,406,814.70 rows=20,320,070 width=10) (actual time=0.074..29,253.689 rows=73,993,067 loops=1)

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

SubPlan (for Seq Scan)

6. 0.000 0.057 ↓ 0.0 0 1

Nested Loop (cost=1.40..36.93 rows=7 width=4) (actual time=0.057..0.057 rows=0 loops=1)

7. 0.006 0.028 ↓ 2.0 2 1

Nested Loop (cost=0.98..27.35 rows=1 width=4) (actual time=0.018..0.028 rows=2 loops=1)

8. 0.003 0.018 ↑ 1.0 2 1

Nested Loop (cost=0.70..26.67 rows=2 width=8) (actual time=0.012..0.018 rows=2 loops=1)

9. 0.009 0.009 ↑ 1.0 2 1

Index Scan using plates_pkey on plates pl (cost=0.42..14.05 rows=2 width=4) (actual time=0.006..0.009 rows=2 loops=1)

  • Index Cond: (id = ANY ('{131849,131837}'::integer[]))
10. 0.006 0.006 ↑ 1.0 1 2

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

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

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

  • Index Cond: (experiment_id = e.id)
12. 0.030 0.030 ↓ 0.0 0 2

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.015..0.015 rows=0 loops=2)

  • Index Cond: (printing_prescription_id = pp.id)
  • Filter: (mutable IS FALSE)
  • Rows Removed by Filter: 40
13. 0.011 9.947 ↑ 39.8 12 1

Hash (cost=2,046.11..2,046.11 rows=478 width=10) (actual time=9.947..9.947 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 9.936 9.936 ↑ 39.8 12 1

Index Scan using printings_plate_id_idx on printings printings_1 (cost=0.56..2,046.11 rows=478 width=10) (actual time=9.128..9.936 rows=12 loops=1)

  • Index Cond: (plate_id = ANY ('{131849,131837}'::integer[]))
15. 178.092 178.092 ↓ 0.0 0 6,596

Index Scan using printings_pkey on printings (cost=0.56..0.87 rows=1 width=10) (actual time=0.027..0.027 rows=0 loops=6,596)

  • Index Cond: (id = pr.printing_id)
  • Filter: (printed_at IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 1.074 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint plate_well_reagents_printing_reagent_id_fkey 79.273 ms 6596 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey 64.613 ms 6596 0.010 ms
check_printing_reagents_mutability_trg 317.059 ms 6596 0.048 ms
platelet_printings_reagents_updated_at 890.225 ms 6596 0.135 ms