explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xByH : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #PTt4; plan #1SCb; plan #3jBG; plan #eE6h; plan #i2qD; plan #tQzX; plan #Mys4; plan #cJmw; plan #SAeq; plan #uq9X; plan #V9og; plan #QmWj4; plan #GiTt; plan #SiSO; plan #f6PS; plan #X2Md; plan #mwvQ; plan #GzsZ; plan #l7nH; plan #rjtQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.059 2.723 ↓ 10.0 10 1

Insert on transfer_reagents (cost=11,140.89..11,144.05 rows=1 width=1,104) (actual time=2.661..2.723 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.139 0.139 ↑ 10.0 10 1

Function Scan on json_to_recordset to_upsert (cost=0.00..1.00 rows=100 width=281) (actual time=0.136..0.139 rows=10 loops=1)

4.          

CTE deleted_transfers

5. 433.398 443.808 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=2.57..11,125.39 rows=2,847 width=34) (actual time=443.808..443.808 rows=0 loops=1)

6. 1.515 10.410 ↑ 1.1 2,664 1

Nested Loop (cost=2.57..11,125.39 rows=2,847 width=34) (actual time=0.029..10.410 rows=2,664 loops=1)

7. 0.011 0.020 ↓ 5.0 5 1

HashAggregate (cost=2.00..2.01 rows=1 width=32) (actual time=0.015..0.020 rows=5 loops=1)

  • Group Key: to_upsert_1.printing_id
8. 0.009 0.009 ↓ 10.0 10 1

CTE Scan on to_upsert to_upsert_1 (cost=0.00..2.00 rows=1 width=32) (actual time=0.004..0.009 rows=10 loops=1)

  • Filter: (printed_at IS NULL)
9. 8.875 8.875 ↑ 5.3 533 5

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations (cost=0.56..11,094.91 rows=2,847 width=10) (actual time=0.012..1.775 rows=533 loops=5)

  • Index Cond: (printing_id = to_upsert_1.printing_id)
10.          

CTE new_transfer_perturbations

11. 2.360 2.458 ↑ 10.0 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=4.75..10.75 rows=100 width=1,435) (actual time=0.444..2.458 rows=10 loops=1)

12. 0.050 0.098 ↑ 10.0 10 1

Subquery Scan on *SELECT* (cost=4.75..10.75 rows=100 width=1,435) (actual time=0.043..0.098 rows=10 loops=1)

13. 0.040 0.048 ↑ 10.0 10 1

HashAggregate (cost=4.75..5.75 rows=100 width=269) (actual time=0.028..0.048 rows=10 loops=1)

  • Group Key: tu_1.well_address, tu_1.src_barcode, tu_1.src_address, tu_1.transfer_volume, tu_1.printing_id, tu_1.condition_id, tu_1.well_type_id, tu_1.control
14. 0.008 0.008 ↑ 10.0 10 1

CTE Scan on to_upsert tu_1 (cost=0.00..2.00 rows=100 width=237) (actual time=0.001..0.008 rows=10 loops=1)

15. 0.038 2.664 ↓ 10.0 10 1

Hash Join (cost=3.75..6.91 rows=1 width=1,104) (actual time=2.640..2.664 rows=10 loops=1)

  • Hash Cond: (((tu.src_barcode)::text = (ntp.src_plate_barcode)::text) AND ((tu.src_address)::text = (ntp.src_well_address)::text) AND (tu.printing_id = ntp.printing_id))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
  • Rows Removed by Join Filter: 8
16. 0.138 0.138 ↑ 10.0 10 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=196) (actual time=0.137..0.138 rows=10 loops=1)

17. 0.013 2.488 ↑ 10.0 10 1

Hash (cost=2.00..2.00 rows=100 width=282) (actual time=2.488..2.488 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 2.475 2.475 ↑ 10.0 10 1

CTE Scan on new_transfer_perturbations ntp (cost=0.00..2.00 rows=100 width=282) (actual time=0.446..2.475 rows=10 loops=1)

Planning time : 0.360 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.460 ms 10 0.046 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 32.285 ms 2664 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 27.475 ms 2664 0.010 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.156 ms 10 0.016 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.106 ms 10 0.011 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.115 ms 10 0.011 ms
check_printing_reagents_mutability_trg on transfer_perturbations 91.034 ms 2664 0.034 ms
platelet_printings_reagents_updated_at on transfer_perturbations 330.383 ms 2664 0.124 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 135.158 ms 2674 0.051 ms