explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.020 4.167 ↑ 100.0 1 1

Insert on transfer_reagents (cost=2,173,322.82..2,465,983.52 rows=100 width=1,104) (actual time=4.165..4.167 rows=1 loops=1)

2.          

CTE to_upsert

3. 0.047 0.047 ↑ 100.0 1 1

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

4.          

CTE deleted_transfers

5. 0.000 0.661 ↓ 0.0 0 1

Delete on transfer_perturbations (cost=107,232.16..1,794,685.18 rows=464,020 width=34) (actual time=0.661..0.661 rows=0 loops=1)

6. 0.001 0.661 ↓ 0.0 0 1

Nested Loop (cost=107,232.16..1,794,685.18 rows=464,020 width=34) (actual time=0.661..0.661 rows=0 loops=1)

7. 0.001 0.660 ↓ 0.0 0 1

Subquery Scan on ANY_subquery (cost=107,231.60..107,234.86 rows=163 width=32) (actual time=0.660..0.660 rows=0 loops=1)

8. 0.000 0.659 ↓ 0.0 0 1

HashAggregate (cost=107,231.60..107,233.23 rows=163 width=4) (actual time=0.659..0.659 rows=0 loops=1)

  • Group Key: to_upsert_1.printing_id
9. 0.002 0.659 ↓ 0.0 0 1

Append (cost=0.00..107,231.19 rows=163 width=4) (actual time=0.659..0.659 rows=0 loops=1)

10. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: (printing_id IS NOT NULL)
  • Rows Removed by Filter: 1
11. 0.004 0.657 ↓ 0.0 0 1

Nested Loop (cost=25.62..107,227.56 rows=63 width=4) (actual time=0.657..0.657 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.653 0.653 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p (cost=25.62..1,072.25 rows=1 width=20) (actual time=0.653..0.653 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. 0.000 0.000 ↓ 0.0 0

Index Scan using printing_reagents_printing_id_idx on transfer_perturbations (cost=0.56..10,323.99 rows=2,847 width=10) (never executed)

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

CTE new_transfer_perturbations

21. 0.000 1.531 ↓ 0.0 0 1

Insert on transfer_perturbations transfer_perturbations_1 (cost=269,282.60..377,317.60 rows=100 width=1,435) (actual time=1.531..1.531 rows=0 loops=1)

22. 0.002 1.531 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=269,282.60..377,317.60 rows=100 width=1,435) (actual time=1.531..1.531 rows=0 loops=1)

23. 0.000 1.529 ↓ 0.0 0 1

GroupAggregate (cost=269,282.60..377,312.60 rows=100 width=269) (actual time=1.529..1.529 rows=0 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.015 1.529 ↓ 0.0 0 1

Sort (cost=269,282.60..269,282.85 rows=100 width=237) (actual time=1.529..1.529 rows=0 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.007 1.514 ↓ 0.0 0 1

Hash Semi Join (cost=107,236.90..269,279.28 rows=100 width=237) (actual time=1.514..1.514 rows=0 loops=1)

  • Hash Cond: (COALESCE(tu_2.printing_id, (SubPlan 10)) = to_upsert_2.printing_id)
26. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

27. 0.000 1.506 ↓ 0.0 0 1

Hash (cost=107,234.86..107,234.86 rows=163 width=4) (actual time=1.506..1.506 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
28. 0.002 1.506 ↓ 0.0 0 1

HashAggregate (cost=107,231.60..107,233.23 rows=163 width=4) (actual time=1.506..1.506 rows=0 loops=1)

  • Group Key: to_upsert_2.printing_id
29. 0.001 1.504 ↓ 0.0 0 1

Append (cost=0.00..107,231.19 rows=163 width=4) (actual time=1.504..1.504 rows=0 loops=1)

30. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: (printing_id IS NOT NULL)
  • Rows Removed by Filter: 1
31. 0.005 1.502 ↓ 0.0 0 1

Nested Loop (cost=25.62..107,227.56 rows=63 width=4) (actual time=1.502..1.502 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.497 1.497 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p_3 (cost=25.62..1,072.25 rows=1 width=20) (actual time=1.497..1.497 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,080.26 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,055.20 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,080.26 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,055.20 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.019 4.147 ↑ 100.0 1 1

Nested Loop Left Join (cost=1,319.04..293,979.73 rows=100 width=1,104) (actual time=4.145..4.147 rows=1 loops=1)

55. 0.010 2.247 ↑ 100.0 1 1

Hash Left Join (cost=3.75..54,019.90 rows=100 width=316) (actual time=2.246..2.247 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.049 0.049 ↑ 100.0 1 1

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

57. 0.002 1.533 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
58. 1.531 1.531 ↓ 0.0 0 1

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

59.          

SubPlan (for Hash Left Join)

60. 0.001 0.655 ↓ 0.0 0 1

Limit (cost=25.62..1,080.26 rows=1 width=4) (actual time=0.655..0.655 rows=0 loops=1)

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.654 0.654 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p_4 (cost=0.56..1,055.20 rows=1 width=4) (actual time=0.654..0.654 rows=0 loops=1)

  • 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.002 1.881 ↓ 0.0 0 1

Bitmap Heap Scan on transfer_perturbations etp (cost=1,315.29..2,399.58 rows=1 width=48) (actual time=1.881..1.881 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)))))
68. 0.002 1.879 ↓ 0.0 0 1

BitmapOr (cost=1,315.29..1,315.29 rows=1 width=0) (actual time=1.879..1.879 rows=0 loops=1)

69. 0.001 0.001 ↓ 0.0 0 1

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

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

BitmapAnd (cost=1,310.46..1,310.46 rows=1 width=0) (actual time=1.876..1.876 rows=0 loops=1)

71. 1.048 1.048 ↓ 356.9 6,781 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.048..1.048 rows=6,781 loops=1)

  • Index Cond: (((tu.src_barcode)::text = (src_plate_barcode)::text) AND ((tu.src_address)::text = (src_well_address)::text))
72. 0.000 0.001 ↓ 0.0 0 1

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

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

SubPlan (for Bitmap Index Scan)

74. 0.002 0.676 ↓ 0.0 0 1

Limit (cost=25.62..1,080.26 rows=1 width=4) (actual time=0.676..0.676 rows=0 loops=1)

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.674 0.674 ↓ 0.0 0 1

Index Scan using printings_plate_id_idx on printings p_5 (cost=0.56..1,055.20 rows=1 width=4) (actual time=0.674..0.674 rows=0 loops=1)

  • 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 : 2.801 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint transfer_reagents_transfer_perturbation_id_fkey on transfer_reagents 0.022 ms 1 0.022 ms