explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rjtQ : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.075 2,481.819 ↓ 10.0 10 1

Insert on transfer_reagents (cost=23,417,668.59..23,417,674.25 rows=1 width=1,104) (actual time=2,481.743..2,481.819 rows=10 loops=1)

2.          

CTE to_upsert

3. 0.142 0.142 ↑ 10.0 10 1

Function Scan on json_to_recordset to_upsert (cost=0.00..1.00 rows=100 width=353) (actual time=0.141..0.142 rows=10 loops=1)

4.          

CTE plates

5. 0.006 0.092 ↑ 100.0 2 1

Unique (cost=1,612.82..1,614.82 rows=200 width=15) (actual time=0.085..0.092 rows=2 loops=1)

6. 0.015 0.086 ↑ 20.0 10 1

Sort (cost=1,612.82..1,613.32 rows=200 width=15) (actual time=0.085..0.086 rows=10 loops=1)

  • Sort Key: pl.id, pl.barcode, pl.experiment_plate_number
  • Sort Method: quicksort Memory: 25kB
7. 0.011 0.071 ↑ 20.0 10 1

Nested Loop (cost=8.25..1,605.17 rows=200 width=15) (actual time=0.017..0.071 rows=10 loops=1)

8. 0.010 0.010 ↑ 10.0 10 1

CTE Scan on to_upsert tu_1 (cost=0.00..2.00 rows=100 width=36) (actual time=0.000..0.010 rows=10 loops=1)

9. 0.020 0.050 ↑ 2.0 1 10

Bitmap Heap Scan on plates pl (cost=8.25..16.01 rows=2 width=15) (actual time=0.005..0.005 rows=1 loops=10)

  • Recheck Cond: ((tu_1.plate_id = id) OR ((tu_1.plate_barcode)::text = (barcode)::text))
  • Heap Blocks: exact=10
10. 0.010 0.030 ↓ 0.0 0 10

BitmapOr (cost=8.25..8.25 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=10)

11. 0.020 0.020 ↑ 1.0 1 10

Bitmap Index Scan on plates_pkey (cost=0.00..4.11 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (tu_1.plate_id = id)
12. 0.000 0.000 ↓ 0.0 0 10

Bitmap Index Scan on idx_plates_barcode (cost=0.00..4.14 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: ((tu_1.plate_barcode)::text = (barcode)::text)
13.          

CTE printings

14. 0.009 2,479.208 ↑ 39.5 6 1

Unique (cost=23,403,179.54..23,403,182.51 rows=237 width=20) (actual time=2,479.196..2,479.208 rows=6 loops=1)

15. 0.026 2,479.199 ↑ 11.8 20 1

Sort (cost=23,403,179.54..23,403,180.14 rows=237 width=20) (actual time=2,479.195..2,479.199 rows=20 loops=1)

  • Sort Key: p.id, p.plate_id, p.layer, p.printed_at
  • Sort Method: quicksort Memory: 25kB
16. 878.468 2,479.173 ↑ 11.8 20 1

Merge Left Join (cost=12.20..23,403,170.20 rows=237 width=20) (actual time=2,459.028..2,479.173 rows=20 loops=1)

  • Merge Cond: (p.plate_id = plates_1.id)
  • Filter: ((tu_2.printing_id = p.id) OR (((tu_2.layer)::text = ((p.layer)::character varying)::text) AND (plates_1.id = p.plate_id) AND (((tu_2.printed_at IS NULL) AND (p.printed_at IS NULL)) OR ((tu_2.printed_at IS NOT NULL) AND (p.printed_at IS NOT NULL)))))
  • Rows Removed by Filter: 1,793,370
17. 910.177 1,600.594 ↑ 426.9 1,793,390 1

Nested Loop (cost=0.56..21,296,954.47 rows=765,526,700 width=64) (actual time=0.155..1,600.594 rows=1,793,390 loops=1)

18. 152.400 152.400 ↑ 42.7 179,339 1

Index Scan using printings_plate_id_idx on printings p (cost=0.56..5,986,419.47 rows=7,655,267 width=20) (actual time=0.010..152.400 rows=179,339 loops=1)

19. 538.017 538.017 ↑ 10.0 10 179,339

CTE Scan on to_upsert tu_2 (cost=0.00..2.00 rows=100 width=44) (actual time=0.000..0.003 rows=10 loops=179,339)

20. 0.018 0.111 ↑ 6.5 31 1

Sort (cost=11.64..12.14 rows=200 width=4) (actual time=0.098..0.111 rows=31 loops=1)

  • Sort Key: plates_1.id
  • Sort Method: quicksort Memory: 25kB
21. 0.093 0.093 ↑ 100.0 2 1

CTE Scan on plates plates_1 (cost=0.00..4.00 rows=200 width=4) (actual time=0.086..0.093 rows=2 loops=1)

22.          

CTE deleted_transfers

23. 1.417 1.484 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=5.31..11,128.13 rows=20,320,070 width=34) (actual time=1.484..1.484 rows=0 loops=1)

24. 0.013 0.067 ↑ 2,032,007.0 10 1

Nested Loop (cost=5.31..11,128.13 rows=20,320,070 width=34) (actual time=0.025..0.067 rows=10 loops=1)

25. 0.009 0.018 ↓ 6.0 6 1

HashAggregate (cost=4.74..4.75 rows=1 width=32) (actual time=0.016..0.018 rows=6 loops=1)

  • Group Key: printings_1.id
26. 0.009 0.009 ↓ 6.0 6 1

CTE Scan on printings printings_1 (cost=0.00..4.74 rows=1 width=32) (actual time=0.007..0.009 rows=6 loops=1)

  • Filter: (printed_at IS NULL)
27. 0.036 0.036 ↑ 1,423.5 2 6

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.005..0.006 rows=2 loops=6)

  • Index Cond: (printing_id = printings_1.id)
28.          

CTE new_transfer_perturbations

29. 2.106 2.350 ↑ 23.8 10 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=1,703.63..1,725.05 rows=238 width=1,435) (actual time=0.606..2.350 rows=10 loops=1)

30. 0.055 0.244 ↑ 23.8 10 1

Subquery Scan on *SELECT* (cost=1,703.63..1,725.05 rows=238 width=1,435) (actual time=0.177..0.244 rows=10 loops=1)

31. 0.040 0.189 ↑ 23.8 10 1

GroupAggregate (cost=1,703.63..1,713.15 rows=238 width=269) (actual time=0.155..0.189 rows=10 loops=1)

  • Group Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_2.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
32. 0.033 0.149 ↑ 23.8 10 1

Sort (cost=1,703.63..1,704.23 rows=238 width=237) (actual time=0.144..0.149 rows=10 loops=1)

  • Sort Key: tu_3.well_address, tu_3.src_barcode, tu_3.src_address, tu_3.transfer_volume, printings_2.id, tu_3.condition_id, tu_3.well_type_id, tu_3.control
  • Sort Method: quicksort Memory: 26kB
33. 0.045 0.116 ↑ 23.8 10 1

Nested Loop (cost=0.00..1,694.24 rows=238 width=237) (actual time=0.055..0.116 rows=10 loops=1)

  • Join Filter: ((printings_2.id = tu_3.printing_id) OR ((((printings_2.layer)::character varying)::text = (tu_3.layer)::text) AND (plates_2.id = printings_2.plate_id)))
  • Rows Removed by Join Filter: 50
34. 0.017 0.017 ↑ 39.5 6 1

CTE Scan on printings printings_2 (cost=0.00..4.74 rows=237 width=12) (actual time=0.001..0.017 rows=6 loops=1)

35. 0.021 0.054 ↑ 20.0 10 6

Materialize (cost=0.00..505.00 rows=200 width=273) (actual time=0.002..0.009 rows=10 loops=6)

36. 0.020 0.033 ↑ 20.0 10 1

Nested Loop Left Join (cost=0.00..504.00 rows=200 width=273) (actual time=0.007..0.033 rows=10 loops=1)

  • Join Filter: ((tu_3.plate_id = plates_2.id) OR ((tu_3.plate_barcode)::text = (plates_2.barcode)::text))
  • Rows Removed by Join Filter: 10
37. 0.003 0.003 ↑ 10.0 10 1

CTE Scan on to_upsert tu_3 (cost=0.00..2.00 rows=100 width=305) (actual time=0.000..0.003 rows=10 loops=1)

38. 0.010 0.010 ↑ 100.0 2 10

CTE Scan on plates plates_2 (cost=0.00..4.00 rows=200 width=222) (actual time=0.000..0.001 rows=2 loops=10)

39. 0.045 2,481.744 ↓ 10.0 10 1

Hash Join (cost=17.08..22.74 rows=1 width=1,104) (actual time=2,481.721..2,481.744 rows=10 loops=1)

  • Hash Cond: (printings.id = ntp.printing_id)
  • Join Filter: ((printings.id = tu.printing_id) OR ((((printings.layer)::character varying)::text = (tu.layer)::text) AND (plates.id = printings.plate_id)))
  • Rows Removed by Join Filter: 8
40. 2,479.201 2,479.201 ↑ 39.5 6 1

CTE Scan on printings (cost=0.00..4.74 rows=237 width=12) (actual time=2,479.198..2,479.201 rows=6 loops=1)

41. 0.015 2.498 ↓ 18.0 18 1

Hash (cost=17.07..17.07 rows=1 width=144) (actual time=2.498..2.498 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
42. 0.033 2.483 ↓ 18.0 18 1

Nested Loop Left Join (cost=3.50..17.07 rows=1 width=144) (actual time=0.640..2.483 rows=18 loops=1)

  • Join Filter: ((plates.id = tu.plate_id) OR ((plates.barcode)::text = (tu.plate_barcode)::text))
  • Rows Removed by Join Filter: 18
43. 0.051 2.432 ↓ 18.0 18 1

Hash Join (cost=3.50..10.07 rows=1 width=176) (actual time=0.635..2.432 rows=18 loops=1)

  • Hash Cond: (((ntp.src_plate_barcode)::text = (tu.src_barcode)::text) AND ((ntp.src_well_address)::text = (tu.src_address)::text))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
  • Rows Removed by Join Filter: 34
44. 2.367 2.367 ↑ 23.8 10 1

CTE Scan on new_transfer_perturbations ntp (cost=0.00..4.76 rows=238 width=282) (actual time=0.608..2.367 rows=10 loops=1)

45. 0.009 0.014 ↑ 10.0 10 1

Hash (cost=2.00..2.00 rows=100 width=264) (actual time=0.014..0.014 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
46. 0.005 0.005 ↑ 10.0 10 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=264) (actual time=0.001..0.005 rows=10 loops=1)

47. 0.018 0.018 ↑ 100.0 2 18

CTE Scan on plates (cost=0.00..4.00 rows=200 width=222) (actual time=0.000..0.001 rows=2 loops=18)

Planning time : 0.947 ms
Execution time : 2,484.766 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.098 ms 10 0.010 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 0.116 ms 10 0.012 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 0.094 ms 10 0.009 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.148 ms 10 0.015 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.112 ms 10 0.011 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.094 ms 10 0.009 ms
check_printing_reagents_mutability_trg on transfer_perturbations 0.227 ms 10 0.023 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.139 ms 10 0.114 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 0.558 ms 20 0.028 ms
platelet_printings_reagents_updated_at on transfer_perturbations 1.552 ms 10 0.155 ms