explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lUw4

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Result (cost=14,780.49..14,780.50 rows=1 width=0) (actual rows= loops=)

2.          

CTE multiple_lock

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=24.70..26.70 rows=200 width=12) (actual rows= loops=)

  • Group Key: product_multiple_lock.product_id, product_multiple_lock.shop_id
4. 0.000 0.000 ↓ 0.0

Seq Scan on product_multiple_lock (cost=0.00..18.40 rows=840 width=12) (actual rows= loops=)

5.          

CTE active_products

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,637.45..10,637.46 rows=1 width=12) (actual rows= loops=)

  • Group Key: p.product_id, ss.shop_id
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,916.85..10,637.45 rows=1 width=12) (actual rows= loops=)

  • Filter: ((pl.product_lock_id IS NULL) OR ((array_length(pl.shop_id, 1) IS NOT NULL) AND (ss.shop_id <> ALL (pl.shop_id))))
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,916.57..10,637.11 rows=1 width=12) (actual rows= loops=)

  • Join Filter: ((pts.product_id = shipment.product_id) AND (pts.shop_id = shipment.shop_id))
  • Filter: ((COALESCE((sum(shipment.amount)), '0'::numeric) - COALESCE((sum(pts.amount)), '0'::numeric)) >= p.pre_quantum)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,524.94..5,894.39 rows=1 width=57) (actual rows= loops=)

  • Hash Cond: ((p.product_id = pml.product_id) AND (ss.shop_id = pml.shop_id))
  • Filter: (pml.multiple_lock_count IS NULL)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,517.94..5,887.05 rows=6 width=57) (actual rows= loops=)

  • Join Filter: (shipment.product_id = p.product_id)
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,517.52..5,827.95 rows=64 width=52) (actual rows= loops=)

  • Join Filter: (ss.region_id = pp.region_id)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,517.10..5,688.25 rows=174 width=48) (actual rows= loops=)

  • Hash Cond: (shipment.shop_id = ss.shop_id)
13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,479.82..5,036.68 rows=44,549 width=12) (actual rows= loops=)

  • Group Key: shipment.shop_id, shipment.product_id
14. 0.000 0.000 ↓ 0.0

Seq Scan on shipment (cost=0.00..3,435.04 rows=139,304 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=37.23..37.23 rows=4 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on shop ss (cost=0.00..37.23 rows=4 width=8) (actual rows= loops=)

  • Filter: is_warehouse
17. 0.000 0.000 ↓ 0.0

Index Scan using price_product_id_region_id_key on price pp (cost=0.42..0.78 rows=2 width=8) (actual rows= loops=)

  • Index Cond: (product_id = shipment.product_id)
  • Filter: ((price > '0'::numeric) OR (rule_price > '0'::numeric))
18. 0.000 0.000 ↓ 0.0

Index Scan using product_pkey on product p (cost=0.42..0.91 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (product_id = pp.product_id)
  • Filter: (product_status_id = ANY ('{4,5,6}'::integer[]))
19. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=16) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

CTE Scan on multiple_lock pml (cost=0.00..4.00 rows=200 width=16) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,391.63..4,494.89 rows=8,261 width=11) (actual rows= loops=)

  • Group Key: pts.shop_id, pts.product_id
22. 0.000 0.000 ↓ 0.0

Seq Scan on position_to_shipment pts (cost=0.00..3,923.36 rows=62,436 width=11) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using product_lock_product_id_key on product_lock pl (cost=0.28..0.31 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (p.product_id = product_id)
24.          

CTE activate_exists_records

25. 0.000 0.000 ↓ 0.0

Update on product_activity pa (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity pa (cost=0.42..8.44 rows=1 width=
27. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap (cost=0.00..0.02 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((product_id = ap.product_id) AND (shop_id = ap.shop_id))
  • Filter: (NOT is_active)
28.          

CTE deactivate_exists_records

29. 0.000 0.000 ↓ 0.0

Update on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

  • Filter: (is_active AND (NOT (hashed SubPlan 4)))
31.          

SubPlan (forSeq Scan)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=4) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity spa (cost=0.42..8.44 rows
33. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((product_id = ap_1.product_id) AND (shop_id = ap_1.shop_id))
34.          

CTE add_new_records

35. 0.000 0.000 ↓ 0.0

Insert on product_activity (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

  • -> Index Only Scan using product_product_activity_product_id_shop_id_unique on product_activity pa_2 (cost=0.42..8.44 rows=1
37. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_2 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((product_id = ap_2.product_id) AND (shop_id = ap_2.shop_id))
38.          

CTE activate_exists_records

39. 0.000 0.000 ↓ 0.0

Update on product_activity pa (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity pa (cost=0.42..8.44 rows=1 width=
41. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap (cost=0.00..0.02 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((product_id = ap.product_id) AND (shop_id = ap.shop_id))
  • Filter: (NOT is_active)
42.          

CTE deactivate_exists_records

43. 0.000 0.000 ↓ 0.0

Update on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

  • Filter: (is_active AND (NOT (hashed SubPlan 4)))
45.          

SubPlan (forSeq Scan)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=4) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity spa (cost=0.42..8.44 rows
47. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((product_id = ap_1.product_id) AND (shop_id = ap_1.shop_id))
48.          

CTE add_new_records

49. 0.000 0.000 ↓ 0.0

Insert on product_activity (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

  • -> Index Only Scan using product_product_activity_product_id_shop_id_unique on product_activity pa_2 (cost=0.42..8.44 rows=1
51. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_2 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((product_id = ap_2.product_id) AND (shop_id = ap_2.shop_id))
52.          

CTE activate_exists_records

53. 0.000 0.000 ↓ 0.0

Update on product_activity pa (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=68) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity pa (cost=0.42..8.44 rows=1 width=
55. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap (cost=0.00..0.02 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((product_id = ap.product_id) AND (shop_id = ap.shop_id))
  • Filter: (NOT is_active)
56.          

CTE deactivate_exists_records

57. 0.000 0.000 ↓ 0.0

Update on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on product_activity pa_1 (cost=8.47..3,009.84 rows=48,420 width=36) (actual rows= loops=)

  • Filter: (is_active AND (NOT (hashed SubPlan 4)))
59.          

SubPlan (forSeq Scan)

60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8.47 rows=1 width=4) (actual rows= loops=)

  • -> Index Scan using product_product_activity_product_id_shop_id_unique on product_activity spa (cost=0.42..8.44 rows
61. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((product_id = ap_1.product_id) AND (shop_id = ap_1.shop_id))
62.          

CTE add_new_records

63. 0.000 0.000 ↓ 0.0

Insert on product_activity (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.42..8.48 rows=1 width=12) (actual rows= loops=)

  • -> Index Only Scan using product_product_activity_product_id_shop_id_unique on product_activity pa_2 (cost=0.42..8.44 rows=1
65. 0.000 0.000 ↓ 0.0

CTE Scan on active_products ap_2 (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((product_id = ap_2.product_id) AND (shop_id = ap_2.shop_id))
66.          

Initplan (forResult)

67. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

CTE Scan on activate_exists_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,089.45..1,089.46 rows=1 width=0) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

CTE Scan on deactivate_exists_records (cost=0.00..968.40 rows=48,420 width=0) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

CTE Scan on add_new_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=16) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

CTE Scan on multiple_lock pml (cost=0.00..4.00 rows=200 width=16) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,391.63..4,494.89 rows=8,261 width=11) (actual rows= loops=)

  • Group Key: pts.shop_id, pts.product_id
76. 0.000 0.000 ↓ 0.0

Seq Scan on position_to_shipment pts (cost=0.00..3,923.36 rows=62,436 width=11) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Index Scan using product_lock_product_id_key on product_lock pl (cost=0.28..0.31 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (p.product_id = product_id)
78. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

CTE Scan on activate_exists_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,089.45..1,089.46 rows=1 width=0) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

CTE Scan on deactivate_exists_records (cost=0.00..968.40 rows=48,420 width=0) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

CTE Scan on add_new_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=16) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

CTE Scan on multiple_lock pml (cost=0.00..4.00 rows=200 width=16) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,391.63..4,494.89 rows=8,261 width=11) (actual rows= loops=)

  • Group Key: pts.shop_id, pts.product_id
87. 0.000 0.000 ↓ 0.0

Seq Scan on position_to_shipment pts (cost=0.00..3,923.36 rows=62,436 width=11) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Index Scan using product_lock_product_id_key on product_lock pl (cost=0.28..0.31 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (p.product_id = product_id)
89. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

CTE Scan on activate_exists_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,089.45..1,089.46 rows=1 width=0) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

CTE Scan on deactivate_exists_records (cost=0.00..968.40 rows=48,420 width=0) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.02..0.03 rows=1 width=0) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

CTE Scan on add_new_records (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)