explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eE6h : 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. 0.045 6.139 ↑ 100.0 1 1

Insert on transfer_reagents (cost=2,161,957.15..2,453,636.24 rows=100 width=1,104) (actual time=6.136..6.139 rows=1 loops=1)

2.          

CTE to_upsert

3. 0.053 0.053 ↑ 100.0 1 1

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

4.          

CTE deleted_transfers

5. 260.861 263.772 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=106,849.68..1,784,691.50 rows=461,173 width=34) (actual time=263.772..263.772 rows=0 loops=1)

6. 0.798 2.911 ↑ 360.0 1,281 1

Nested Loop (cost=106,849.68..1,784,691.50 rows=461,173 width=34) (actual time=0.051..2.911 rows=1,281 loops=1)

7. 0.004 0.042 ↑ 162.0 1 1

Subquery Scan on ANY_subquery (cost=106,849.12..106,852.36 rows=162 width=32) (actual time=0.039..0.042 rows=1 loops=1)

8. 0.005 0.038 ↑ 162.0 1 1

HashAggregate (cost=106,849.12..106,850.74 rows=162 width=4) (actual time=0.036..0.038 rows=1 loops=1)

  • Group Key: to_upsert_1.printing_id
9. 0.001 0.033 ↑ 162.0 1 1

Append (cost=0.00..106,848.71 rows=162 width=4) (actual time=0.002..0.033 rows=1 loops=1)

10. 0.003 0.003 ↑ 100.0 1 1

CTE Scan on to_upsert to_upsert_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (printing_id IS NOT NULL)
11. 0.002 0.029 ↓ 0.0 0 1

Nested Loop (cost=25.62..106,845.09 rows=62 width=4) (actual time=0.029..0.029 rows=0 loops=1)

12. 0.000 0.000 ↑ 100.0 1 1

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

13. 0.027 0.027 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p (cost=25.62..1,068.42 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: (plate_id = COALESCE(tu_1.plate_id, (SubPlan 2), (SubPlan 3)))
  • Filter: (CASE WHEN (tu_1.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND ((tu_1.layer)::text = ((layer)::character varying)::text))
14.          

SubPlan (for Index Scan)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu_1.plate_barcode)::text)
16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu_1.experiment_label)::text)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_1 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e.id) AND (experiment_plate_number = tu_1.plate_number))
19. 2.071 2.071 ↑ 2.2 1,281 1

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations (cost=0.56..10,328.56 rows=2,847 width=10) (actual time=0.011..2.071 rows=1,281 loops=1)

  • Index Cond: (printing_id = "ANY_subquery".printing_id)
20.          

CTE new_transfer_perturbations

21. 3.018 4.390 ↑ 100.0 1 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=268,308.76..375,949.55 rows=100 width=1,435) (actual time=4.388..4.390 rows=1 loops=1)

22. 0.025 1.372 ↑ 100.0 1 1

Subquery Scan on *SELECT* (cost=268,308.76..375,949.55 rows=100 width=1,435) (actual time=1.371..1.372 rows=1 loops=1)

23. 0.010 1.347 ↑ 100.0 1 1

GroupAggregate (cost=268,308.76..375,944.55 rows=100 width=269) (actual time=1.347..1.347 rows=1 loops=1)

  • Group Key: tu_2.well_address, tu_2.src_barcode, tu_2.src_address, tu_2.transfer_volume, (COALESCE(tu_2.printing_id, (SubPlan 7))), tu_2.condition_id, tu_2.well_type_id, tu_2.control
24. 0.019 1.337 ↑ 100.0 1 1

Sort (cost=268,308.76..268,309.01 rows=100 width=237) (actual time=1.337..1.337 rows=1 loops=1)

  • Sort Key: tu_2.well_address, tu_2.src_barcode, tu_2.src_address, tu_2.transfer_volume, (COALESCE(tu_2.printing_id, (SubPlan 7))), tu_2.condition_id, tu_2.well_type_id, tu_2.control
  • Sort Method: quicksort Memory: 25kB
25. 0.013 1.318 ↑ 100.0 1 1

Hash Semi Join (cost=106,854.38..268,305.44 rows=100 width=237) (actual time=1.317..1.318 rows=1 loops=1)

  • Hash Cond: (COALESCE(tu_2.printing_id, (SubPlan 10)) = to_upsert_2.printing_id)
26. 0.002 0.002 ↑ 100.0 1 1

CTE Scan on to_upsert tu_2 (cost=0.00..2.00 rows=100 width=349) (actual time=0.001..0.002 rows=1 loops=1)

27. 0.006 1.303 ↑ 162.0 1 1

Hash (cost=106,852.36..106,852.36 rows=162 width=4) (actual time=1.303..1.303 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.003 1.297 ↑ 162.0 1 1

HashAggregate (cost=106,849.12..106,850.74 rows=162 width=4) (actual time=1.297..1.297 rows=1 loops=1)

  • Group Key: to_upsert_2.printing_id
29. 0.001 1.294 ↑ 162.0 1 1

Append (cost=0.00..106,848.71 rows=162 width=4) (actual time=0.002..1.294 rows=1 loops=1)

30. 0.003 0.003 ↑ 100.0 1 1

CTE Scan on to_upsert to_upsert_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (printing_id IS NOT NULL)
31. 0.005 1.290 ↓ 0.0 0 1

Nested Loop (cost=25.62..106,845.09 rows=62 width=4) (actual time=1.290..1.290 rows=0 loops=1)

32. 0.000 0.000 ↑ 100.0 1 1

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

33. 1.285 1.285 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p_3 (cost=25.62..1,068.42 rows=1 width=20) (actual time=1.285..1.285 rows=0 loops=1)

  • Index Cond: (plate_id = COALESCE(tu_3.plate_id, (SubPlan 11), (SubPlan 12)))
  • Filter: (CASE WHEN (tu_3.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND ((tu_3.layer)::text = ((layer)::character varying)::text))
34.          

SubPlan (for Index Scan)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl_6 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu_3.plate_barcode)::text)
36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e_3 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu_3.experiment_label)::text)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_7 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e_3.id) AND (experiment_plate_number = tu_3.plate_number))
39.          

SubPlan (for Hash Semi Join)

40. 0.000 0.000 ↓ 0.0 0

Limit (cost=25.62..1,076.32 rows=1 width=4) (never executed)

41.          

Initplan (for Limit)

42. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl_2 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu_2.plate_barcode)::text)
43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e_1 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu_2.experiment_label)::text)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_3 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e_1.id) AND (experiment_plate_number = tu_2.plate_number))
46. 0.000 0.000 ↓ 0.0 0

Index Scan using printings_plate_id_idx on printings p_1 (cost=0.56..1,051.26 rows=1 width=4) (never executed)

  • Index Cond: (plate_id = COALESCE(tu_2.plate_id, $15, $19))
  • Filter: (CASE WHEN (tu_2.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND (((layer)::character varying)::text = (tu_2.layer)::text))
47. 0.000 0.000 ↓ 0.0 0

Limit (cost=25.62..1,076.32 rows=1 width=4) (never executed)

48.          

Initplan (for Limit)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl_4 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu_2.plate_barcode)::text)
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e_2 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu_2.experiment_label)::text)
52. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_5 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e_2.id) AND (experiment_plate_number = tu_2.plate_number))
53. 0.000 0.000 ↓ 0.0 0

Index Scan using printings_plate_id_idx on printings p_2 (cost=0.56..1,051.26 rows=1 width=4) (never executed)

  • Index Cond: (plate_id = COALESCE(tu_2.plate_id, $24, $28))
  • Filter: (CASE WHEN (tu_2.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND (((layer)::character varying)::text = (tu_2.layer)::text))
54. 0.017 6.094 ↑ 100.0 1 1

Nested Loop Left Join (cost=1,315.09..292,994.19 rows=100 width=1,104) (actual time=6.091..6.094 rows=1 loops=1)

55. 0.013 4.464 ↑ 100.0 1 1

Hash Left Join (cost=3.75..53,822.79 rows=100 width=316) (actual time=4.463..4.464 rows=1 loops=1)

  • Hash Cond: (((tu.src_barcode)::text = (ntp.src_plate_barcode)::text) AND ((tu.src_address)::text = (ntp.src_well_address)::text) AND (COALESCE(tu.printing_id, (SubPlan 16)) = ntp.printing_id))
  • Join Filter: ((tu.well_address)::text = ANY ((ntp.addresses)::text[]))
56. 0.055 0.055 ↑ 100.0 1 1

CTE Scan on to_upsert tu (cost=0.00..2.00 rows=100 width=312) (actual time=0.054..0.055 rows=1 loops=1)

57. 0.004 4.396 ↑ 100.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 4.392 4.392 ↑ 100.0 1 1

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

59.          

SubPlan (for Hash Left Join)

60. 0.000 0.000 ↓ 0.0 0

Limit (cost=25.62..1,076.32 rows=1 width=4) (never executed)

61.          

Initplan (for Limit)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl_8 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu.plate_barcode)::text)
63. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e_4 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu.experiment_label)::text)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_9 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e_4.id) AND (experiment_plate_number = tu.plate_number))
66. 0.000 0.000 ↓ 0.0 0

Index Scan using printings_plate_id_idx on printings p_4 (cost=0.56..1,051.26 rows=1 width=4) (never executed)

  • Index Cond: (plate_id = COALESCE(tu.plate_id, $45, $49))
  • Filter: (CASE WHEN (tu.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND (((layer)::character varying)::text = (tu.layer)::text))
67. 0.006 1.613 ↓ 0.0 0 1

Bitmap Heap Scan on transfer_perturbations etp (cost=1,311.34..2,391.69 rows=1 width=48) (actual time=1.613..1.613 rows=0 loops=1)

  • Recheck Cond: ((tu.transfer_perturbation_id = id) OR (((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text) AND (printing_id = COALESCE(tu.printing_id, (SubPlan 19)))))
  • Filter: ((tu.transfer_perturbation_id = id) OR (((tu.well_address)::text = ANY ((addresses)::text[])) AND ((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text) AND (printing_id = COALESCE(tu.printing_id, (SubPlan 19)))))
  • Heap Blocks: exact=1
68. 0.006 1.607 ↓ 0.0 0 1

BitmapOr (cost=1,311.34..1,311.34 rows=1 width=0) (actual time=1.607..1.607 rows=0 loops=1)

69. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on printing_reagents_pkey (cost=0.00..4.57 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (tu.transfer_perturbation_id = id)
70. 0.324 1.601 ↓ 0.0 0 1

BitmapAnd (cost=1,306.52..1,306.52 rows=1 width=0) (actual time=1.601..1.601 rows=0 loops=1)

71. 1.091 1.091 ↓ 391.7 7,442 1

Bitmap Index Scan on printing_reagents_src_plate_barcode_src_well_address_idx (cost=0.00..4.76 rows=19 width=0) (actual time=1.091..1.091 rows=7,442 loops=1)

  • Index Cond: (((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text))
72. 0.186 0.186 ↑ 2.2 1,282 1

Bitmap Index Scan on printing_reagents_printing_id_idx (cost=0.00..1,301.52 rows=2,847 width=0) (actual time=0.186..0.186 rows=1,282 loops=1)

  • Index Cond: (printing_id = COALESCE(tu.printing_id, (SubPlan 19)))
73.          

SubPlan (for Bitmap Index Scan)

74. 0.000 0.000 ↓ 0.0 0

Limit (cost=25.62..1,076.32 rows=1 width=4) (never executed)

75.          

Initplan (for Limit)

76. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_plates_barcode on plates pl_10 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((barcode)::text = (tu.plate_barcode)::text)
77. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.63 rows=1 width=4) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Scan using experiments_label_key on experiments e_5 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((label)::text = (tu.experiment_label)::text)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_experiment_id_and_plate_number on plates pl_11 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: ((experiment_id = e_5.id) AND (experiment_plate_number = tu.plate_number))
80. 0.000 0.000 ↓ 0.0 0

Index Scan using printings_plate_id_idx on printings p_5 (cost=0.56..1,051.26 rows=1 width=4) (never executed)

  • Index Cond: (plate_id = COALESCE(tu.plate_id, $54, $58))
  • Filter: (CASE WHEN (tu.printed_at IS NULL) THEN (printed_at IS NULL) ELSE (printed_at IS NOT NULL) END AND (((layer)::character varying)::text = (tu.layer)::text))
Planning time : 3.132 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.124 ms 1 0.124 ms
for constraint plate_well_reagents_printing_reagent_id_fkey on transfer_perturbations 18.840 ms 1281 0.015 ms
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_perturbations 15.775 ms 1281 0.012 ms
for constraint printing_prescription_conditions_id_fkey on transfer_perturbations 0.121 ms 1 0.121 ms
for constraint printing_reagents_well_type_id_fkey on transfer_perturbations 0.069 ms 1 0.069 ms
for constraint printing_reagents_printing_id_fkey on transfer_perturbations 0.149 ms 1 0.149 ms
check_printing_reagents_mutability_trg on transfer_perturbations 65.126 ms 1281 0.051 ms
platelet_printings_reagents_updated_at on transfer_perturbations 182.018 ms 1281 0.142 ms
trg_stash_experiment_version_printing_reagents on transfer_perturbations 91.128 ms 1282 0.071 ms
platelet_printings_reagents_updated_at on transfer_perturbations 2.871 ms 1 2.871 ms