explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1SCb : Optimization for: plan #PTt4

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,547.916 49,813.574 ↓ 0.0 0 1

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

2. 3.742 48,265.658 ↓ 6.9 4,578 1

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

3. 16,474.713 47,849.896 ↓ 1.5 4,578 1

Hash Semi Join (cost=3,337.24..3,476,249.87 rows=3,016 width=16) (actual time=44,071.527..47,849.896 rows=4,578 loops=1)

  • Hash Cond: (pr.printing_id = printings_1.id)
4. 31,374.281 31,374.339 ↓ 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.070..31,374.339 rows=73,757,177 loops=1)

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

SubPlan (for Seq Scan)

6. 0.002 0.058 ↓ 0.0 0 1

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

7. 0.004 0.041 ↓ 3.0 3 1

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

8. 0.005 0.028 ↑ 1.0 3 1

Nested Loop (cost=0.70..36.60 rows=3 width=8) (actual time=0.016..0.028 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.015 0.015 ↓ 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.005..0.005 rows=0 loops=3)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.840 0.840 ↑ 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.010..0.840 rows=9 loops=1)

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

Index Scan using printings_pkey on printings (cost=0.56..0.87 rows=1 width=10) (actual time=0.090..0.090 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.104 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint plate_well_reagents_printing_reagent_id_fkey 62.476 ms 4578 0.014 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey 53.128 ms 4578 0.012 ms
check_printing_reagents_mutability_trg 520.980 ms 4578 0.114 ms
platelet_printings_reagents_updated_at 998.853 ms 4578 0.218 ms
trg_stash_experiment_version_printing_reagents 640.104 ms 4578 0.140 ms