explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dL8F

Settings
# exclusive inclusive rows x rows loops node
1. 0.271 2,977.487 ↑ 192.0 1 1

GroupAggregate (cost=2,221,592.85..7,420,076.72 rows=192 width=148) (actual time=2,977.487..2,977.487 rows=1 loops=1)

  • Group Key: t0.id, nns.id
2.          

CTE paths

3. 5.761 5.761 ↑ 1,000.0 1 1

Function Scan on get_custom_values_path paths_1 (cost=0.25..22.75 rows=1,000 width=208) (actual time=5.761..5.761 rows=1 loops=1)

4.          

CTE customer_paths

5. 1.074 1,288.943 ↑ 402.4 2,097 1

Recursive Union (cost=0.00..1,444,781.68 rows=843,861 width=61) (actual time=0.013..1,288.943 rows=2,097 loops=1)

6. 0.851 0.851 ↑ 1.0 230 1

Seq Scan on customer (cost=0.00..158.01 rows=231 width=61) (actual time=0.011..0.851 rows=230 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,972
7. 933.888 1,287.018 ↑ 135.6 622 3

Nested Loop (cost=0.00..142,774.64 rows=84,363 width=61) (actual time=63.792..429.006 rows=622 loops=3)

  • Join Filter: ((customer_paths.level < customer_1.level) AND (customer_paths.id = ANY (customer_1.parent_ids)))
  • Rows Removed by Join Filter: 1,538,576
8. 0.834 0.834 ↑ 3.3 699 3

WorkTable Scan on customer_paths (cost=0.00..46.20 rows=2,310 width=38) (actual time=0.001..0.278 rows=699 loops=3)

9. 351.361 352.296 ↑ 1.0 2,202 2,097

Materialize (cost=0.00..163.62 rows=2,241 width=29) (actual time=0.000..0.168 rows=2,202 loops=2,097)

10. 0.935 0.935 ↑ 1.0 2,202 1

Seq Scan on customer customer_1 (cost=0.00..152.41 rows=2,241 width=29) (actual time=0.004..0.935 rows=2,202 loops=1)

11.          

CTE customer_parents

12. 0.033 1,291.462 ↑ 4,014.0 1 1

GroupAggregate (cost=74,057.63..78,326.81 rows=4,014 width=38) (actual time=1,291.462..1,291.462 rows=1 loops=1)

  • Group Key: customer_paths_1.id, customer_paths_1.level
13. 0.018 1,291.429 ↑ 210,950.0 2 1

Sort (cost=74,057.63..75,112.38 rows=421,900 width=10) (actual time=1,291.429..1,291.429 rows=2 loops=1)

  • Sort Key: customer_paths_1.level
  • Sort Method: quicksort Memory: 25kB
14. 0.006 1,291.411 ↑ 210,950.0 2 1

Nested Loop (cost=0.00..27,424.87 rows=421,900 width=10) (actual time=151.276..1,291.411 rows=2 loops=1)

15. 1,291.383 1,291.383 ↑ 4,219.0 1 1

CTE Scan on customer_paths customer_paths_1 (cost=0.00..18,986.87 rows=4,219 width=38) (actual time=151.250..1,291.383 rows=1 loops=1)

  • Filter: (id = 482)
  • Rows Removed by Filter: 2,096
16. 0.022 0.022 ↑ 50.0 2 1

Function Scan on unnest p (cost=0.00..1.00 rows=100 width=4) (actual time=0.021..0.022 rows=2 loops=1)

17.          

CTE ninushis_customers

18. 0.562 1,323.211 ↑ 22.3 82 1

HashAggregate (cost=19,993.16..20,011.47 rows=1,831 width=8) (actual time=1,323.184..1,323.211 rows=82 loops=1)

  • Group Key: ninushi_customer.ninushi_id, ninushi_customer.customer_id
19. 0.154 1,322.649 ↑ 1.1 1,716 1

Append (cost=7,139.39..19,984.01 rows=1,831 width=8) (actual time=1,291.536..1,322.649 rows=1,716 loops=1)

20. 0.300 1,292.061 ↑ 20.5 44 1

Hash Join (cost=7,139.39..7,224.23 rows=903 width=8) (actual time=1,291.535..1,292.061 rows=44 loops=1)

  • Hash Cond: (ninushi_customer.customer_id = (unnest(customer_parents_1.parents_and_self)))
21. 0.279 0.279 ↓ 1.0 1,814 1

Seq Scan on ninushi_customer (cost=0.00..70.06 rows=1,806 width=8) (actual time=0.007..0.279 rows=1,814 loops=1)

22. 0.005 1,291.482 ↑ 100.0 2 1

Hash (cost=7,136.89..7,136.89 rows=200 width=4) (actual time=1,291.482..1,291.482 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.006 1,291.477 ↑ 100.0 2 1

HashAggregate (cost=7,134.89..7,136.89 rows=200 width=4) (actual time=1,291.476..1,291.477 rows=2 loops=1)

  • Group Key: unnest(customer_parents_1.parents_and_self)
24. 0.006 1,291.471 ↑ 200,700.0 2 1

ProjectSet (cost=0.00..2,117.39 rows=401,400 width=4) (actual time=1,291.469..1,291.471 rows=2 loops=1)

25. 1,291.465 1,291.465 ↑ 4,014.0 1 1

CTE Scan on customer_parents customer_parents_1 (cost=0.00..80.28 rows=4,014 width=32) (actual time=1,291.465..1,291.465 rows=1 loops=1)

26. 21.265 30.434 ↓ 1.8 1,672 1

Nested Loop (cost=7,300.73..12,741.46 rows=928 width=8) (actual time=7.026..30.434 rows=1,672 loops=1)

  • Join Filter: (market.id = ANY (buyer.parent_ids))
  • Rows Removed by Join Filter: 95,216
27. 0.361 0.361 ↑ 1.0 2,202 1

Seq Scan on customer buyer (cost=0.00..152.41 rows=2,241 width=27) (actual time=0.010..0.361 rows=2,202 loops=1)

28. 7.424 8.808 ↑ 2.1 44 2,202

Materialize (cost=7,300.73..7,378.96 rows=93 width=8) (actual time=0.000..0.004 rows=44 loops=2,202)

29. 0.303 1.384 ↑ 2.1 44 1

Hash Join (cost=7,300.73..7,378.50 rows=93 width=8) (actual time=0.884..1.384 rows=44 loops=1)

  • Hash Cond: (nnc.customer_id = market.id)
30. 0.241 0.241 ↓ 1.0 1,814 1

Seq Scan on ninushi_customer nnc (cost=0.00..70.06 rows=1,806 width=4) (actual time=0.003..0.241 rows=1,814 loops=1)

31. 0.003 0.840 ↑ 116.0 1 1

Hash (cost=7,299.28..7,299.28 rows=116 width=12) (actual time=0.840..0.840 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.037 0.837 ↑ 116.0 1 1

Hash Join (cost=7,139.39..7,299.28 rows=116 width=12) (actual time=0.162..0.837 rows=1 loops=1)

  • Hash Cond: (market.id = (unnest(customer_parents_2.parents_and_self)))
33. 0.781 0.781 ↑ 1.0 230 1

Seq Scan on customer market (cost=0.00..158.01 rows=231 width=8) (actual time=0.004..0.781 rows=230 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,972
34. 0.010 0.019 ↑ 100.0 2 1

Hash (cost=7,136.89..7,136.89 rows=200 width=4) (actual time=0.019..0.019 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.005 0.009 ↑ 100.0 2 1

HashAggregate (cost=7,134.89..7,136.89 rows=200 width=4) (actual time=0.007..0.009 rows=2 loops=1)

  • Group Key: unnest(customer_parents_2.parents_and_self)
36. 0.003 0.004 ↑ 200,700.0 2 1

ProjectSet (cost=0.00..2,117.39 rows=401,400 width=4) (actual time=0.003..0.004 rows=2 loops=1)

37. 0.001 0.001 ↑ 4,014.0 1 1

CTE Scan on customer_parents customer_parents_2 (cost=0.00..80.28 rows=4,014 width=32) (actual time=0.001..0.001 rows=1 loops=1)

38. 0.018 2,977.151 ↑ 438,640.0 1 1

Sort (cost=678,450.14..679,546.74 rows=438,640 width=1,050) (actual time=2,977.151..2,977.151 rows=1 loops=1)

  • Sort Key: t0.id, nns.id
  • Sort Method: quicksort Memory: 33kB
39. 0.009 2,977.133 ↑ 438,640.0 1 1

Merge Join (cost=80,183.95..232,540.70 rows=438,640 width=1,050) (actual time=2,977.111..2,977.133 rows=1 loops=1)

  • Merge Cond: (nns.id = ninushis_customers.ninushi_id)
40. 0.006 2,977.050 ↑ 438,640.0 1 1

Nested Loop (cost=80,131.11..227,003.86 rows=438,640 width=1,054) (actual time=2,977.028..2,977.050 rows=1 loops=1)

41. 0.005 2,977.029 ↑ 14,341.0 1 1

Nested Loop (cost=80,131.11..212,078.55 rows=14,341 width=1,028) (actual time=2,977.017..2,977.029 rows=1 loops=1)

42. 0.048 2,976.990 ↑ 81.0 1 1

Nested Loop (cost=80,131.11..211,031.21 rows=81 width=798) (actual time=2,976.987..2,976.990 rows=1 loops=1)

  • Join Filter: (t0.ninushi_id = nns.id)
  • Rows Removed by Join Filter: 100
43. 0.068 0.068 ↓ 1.1 101 1

Index Scan using ninushi_pkey on ninushi nns (cost=0.14..26.05 rows=96 width=16) (actual time=0.019..0.068 rows=101 loops=1)

44. 0.013 2,976.874 ↑ 81.0 1 101

Materialize (cost=80,130.97..210,888.72 rows=81 width=782) (actual time=19.239..29.474 rows=1 loops=101)

45. 0.009 2,976.861 ↑ 81.0 1 1

Hash Left Join (cost=80,130.97..210,888.32 rows=81 width=782) (actual time=1,943.080..2,976.861 rows=1 loops=1)

  • Hash Cond: (zai.region_id = prg.id)
46. 0.125 2,976.703 ↑ 81.0 1 1

Hash Join (cost=80,119.37..210,875.70 rows=81 width=757) (actual time=1,942.923..2,976.703 rows=1 loops=1)

  • Hash Cond: (zai.group_id = t0.id)
  • Join Filter: (COALESCE(COALESCE(extract_custom_prices(zai.custom_prices, jsonb_array_to_text_array((paths.root_level_paths_price_by_nns_id -> (zai.ninushi_id)::text)), paths.nested_paths_price, paths.nested_cutoff), zai.price), zai.price) > '0'::double precision)
47. 0.003 1,033.921 ↑ 4.6 9 1

Append (cost=0.00..130,623.10 rows=41 width=548) (actual time=0.033..1,033.921 rows=9 loops=1)

48. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on zaiko zai (cost=0.00..0.00 rows=1 width=344) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((ikkatsu_id IS NOT NULL) AND (id = orgid) AND (orgid = ikkatsu_id))
49. 1,033.912 1,033.912 ↑ 4.4 9 1

Seq Scan on zaiko zai_1 (cost=0.00..130,623.10 rows=40 width=553) (actual time=0.026..1,033.912 rows=9 loops=1)

  • Filter: ((ikkatsu_id IS NOT NULL) AND (id = orgid) AND (orgid = ikkatsu_id))
  • Rows Removed by Filter: 1,040,591
50. 0.005 1,942.657 ↑ 244.0 1 1

Hash (cost=80,116.32..80,116.32 rows=244 width=216) (actual time=1,942.657..1,942.657 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
51. 0.004 1,942.652 ↑ 244.0 1 1

Nested Loop (cost=79,924.85..80,116.32 rows=244 width=216) (actual time=1,942.650..1,942.652 rows=1 loops=1)

  • Join Filter: (paths.parent_id = ANY ((array_agg(zps.market_id))))
52. 0.110 1,936.868 ↑ 5.0 1 1

Merge Join (cost=79,924.85..79,943.82 rows=5 width=116) (actual time=1,936.867..1,936.868 rows=1 loops=1)

  • Merge Cond: (zps.zaiko_group_id = t0.id)
53. 1.087 1,936.693 ↓ 2.8 1,497 1

GroupAggregate (cost=79,884.39..79,896.54 rows=540 width=40) (actual time=1,935.519..1,936.693 rows=1,497 loops=1)

  • Group Key: zps.zaiko_group_id, zps.customer_id
54. 1.073 1,935.606 ↓ 2.8 1,497 1

Sort (cost=79,884.39..79,885.74 rows=540 width=12) (actual time=1,935.508..1,935.606 rows=1,497 loops=1)

  • Sort Key: zps.zaiko_group_id
  • Sort Method: quicksort Memory: 119kB
55. 0.829 1,934.533 ↓ 2.8 1,497 1

Nested Loop Semi Join (cost=59.51..79,859.88 rows=540 width=12) (actual time=1,648.591..1,934.533 rows=1,497 loops=1)

56. 2.605 1,933.704 ↓ 2.8 1,497 1

Hash Join (cost=59.51..72,701.47 rows=540 width=12) (actual time=1,648.565..1,933.704 rows=1,497 loops=1)

  • Hash Cond: (zps.ninushi_id = nnscs.ninushi_id)
  • Join Filter: ((nnscs.customer_id = zps.market_id) OR (nnscs.customer_id = zps.customer_id))
  • Rows Removed by Join Filter: 2,836
57. 0.771 607.828 ↑ 1.3 4,539 1

Append (cost=0.00..70,461.48 rows=5,916 width=16) (actual time=2.694..607.828 rows=4,539 loops=1)

58. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on zaiko_publish zps (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (customer_id = 482)
59. 607.055 607.055 ↑ 1.3 4,539 1

Seq Scan on zaiko_publish zps_1 (cost=0.00..70,461.48 rows=5,915 width=16) (actual time=2.691..607.055 rows=4,539 loops=1)

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 3,543,114
60. 0.027 1,323.271 ↑ 22.3 82 1

Hash (cost=36.62..36.62 rows=1,831 width=8) (actual time=1,323.271..1,323.271 rows=82 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 20kB
61. 1,323.244 1,323.244 ↑ 22.3 82 1

CTE Scan on ninushis_customers nnscs (cost=0.00..36.62 rows=1,831 width=8) (actual time=1,323.186..1,323.244 rows=82 loops=1)

62. 0.000 0.000 ↑ 2,007.0 1 1,497

Materialize (cost=0.00..7,144.92 rows=2,007 width=4) (actual time=0.000..0.000 rows=1 loops=1,497)

63. 0.002 0.011 ↑ 2,007.0 1 1

Subquery Scan on ANY_subquery (cost=0.00..7,134.89 rows=2,007 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: ("ANY_subquery".unnest = 482)
  • Rows Removed by Filter: 1
64. 0.008 0.009 ↑ 200,700.0 2 1

ProjectSet (cost=0.00..2,117.39 rows=401,400 width=4) (actual time=0.009..0.009 rows=2 loops=1)

65. 0.001 0.001 ↑ 4,014.0 1 1

CTE Scan on customer_parents (cost=0.00..80.28 rows=4,014 width=32) (actual time=0.001..0.001 rows=1 loops=1)

66. 0.018 0.065 ↑ 1.0 2 1

Sort (cost=40.46..40.47 rows=2 width=80) (actual time=0.064..0.065 rows=2 loops=1)

  • Sort Key: t0.id
  • Sort Method: quicksort Memory: 25kB
67. 0.001 0.047 ↑ 1.0 2 1

Append (cost=0.00..40.45 rows=2 width=80) (actual time=0.038..0.047 rows=2 loops=1)

68. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on simple_proposal t0 (cost=0.00..0.00 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((is_fake = 0) AND (active = 1) AND (CASE WHEN ((deadline ->> 'type'::text) = '1'::text) THEN concat((deadline ->> 'date'::text), ' ', (deadline ->> 'time'::text), ':00') ELSE concat((auction_date_end - ((deadline ->> 'day_before'::text))::integer), ' ', (deadline ->> 'time'::text), ':00') END > (now())::text))
69. 0.042 0.042 ↓ 2.0 2 1

Seq Scan on simple_proposal t0_1 (cost=0.00..40.45 rows=1 width=80) (actual time=0.034..0.042 rows=2 loops=1)

  • Filter: ((is_fake = 0) AND (active = 1) AND (CASE WHEN ((deadline ->> 'type'::text) = '1'::text) THEN concat((deadline ->> 'date'::text), ' ', (deadline ->> 'time'::text), ':00') ELSE concat((auction_date_end - ((deadline ->> 'day_before'::text))::integer), ' ', (deadline ->> 'time'::text), ':00') END > (now())::text))
70. 5.780 5.780 ↑ 1,000.0 1 1

CTE Scan on paths (cost=0.00..20.00 rows=1,000 width=136) (actual time=5.779..5.780 rows=1 loops=1)

71. 0.084 0.149 ↑ 1.0 293 1

Hash (cost=7.93..7.93 rows=293 width=29) (actual time=0.149..0.149 rows=293 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
72. 0.065 0.065 ↑ 1.0 293 1

Seq Scan on region prg (cost=0.00..7.93 rows=293 width=29) (actual time=0.007..0.065 rows=293 loops=1)

73. 0.002 0.034 ↑ 3.0 1 1

Append (cost=0.00..12.90 rows=3 width=231) (actual time=0.025..0.034 rows=1 loops=1)

74. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on catalog variety (cost=0.00..0.00 rows=1 width=106) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((active = 1) AND (zai.catalog_id = id))
75. 0.021 0.021 ↑ 1.0 1 1

Index Scan using catalog_pkey on catalog variety_1 (cost=0.29..8.02 rows=1 width=236) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = zai.catalog_id)
  • Filter: (active = 1)
76. 0.008 0.008 ↓ 0.0 0 1

Index Scan using catalog_brand_pkey on catalog_brand variety_2 (cost=0.28..4.88 rows=1 width=106) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (id = zai.catalog_id)
  • Filter: (active = 1)
77. 0.002 0.015 ↑ 3.0 1 1

Append (cost=0.00..1.01 rows=3 width=30) (actual time=0.005..0.015 rows=1 loops=1)

78. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on catalog flower (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((brand IS NULL) AND (active = 1) AND (variety.brand = id))
79. 0.004 0.004 ↑ 1.0 1 1

Index Scan using catalog_pkey on catalog flower_1 (cost=0.29..0.70 rows=1 width=28) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = variety.brand)
  • Filter: ((brand IS NULL) AND (active = 1))
80. 0.008 0.008 ↓ 0.0 0 1

Index Scan using catalog_brand_pkey on catalog_brand flower_2 (cost=0.28..0.31 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (id = variety.brand)
  • Filter: ((brand IS NULL) AND (active = 1))
81. 0.024 0.074 ↑ 4.5 44 1

Sort (cost=52.84..53.34 rows=200 width=4) (actual time=0.072..0.074 rows=44 loops=1)

  • Sort Key: ninushis_customers.ninushi_id
  • Sort Method: quicksort Memory: 27kB
82. 0.039 0.050 ↑ 4.4 45 1

HashAggregate (cost=41.20..43.20 rows=200 width=4) (actual time=0.041..0.050 rows=45 loops=1)

  • Group Key: ninushis_customers.ninushi_id
83. 0.011 0.011 ↑ 22.3 82 1

CTE Scan on ninushis_customers (cost=0.00..36.62 rows=1,831 width=4) (actual time=0.001..0.011 rows=82 loops=1)

84.          

SubPlan (for GroupAggregate)

85. 0.004 0.036 ↑ 1.0 1 1

Aggregate (cost=1.30..1.31 rows=1 width=32) (actual time=0.036..0.036 rows=1 loops=1)

86. 0.032 0.032 ↑ 50.0 2 1

Function Scan on unnest x (cost=0.05..1.05 rows=100 width=32) (actual time=0.031..0.032 rows=2 loops=1)

87. 0.005 0.007 ↑ 1.0 1 1

Aggregate (cost=1.26..1.27 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)

88. 0.002 0.002 ↑ 100.0 1 1

Function Scan on unnest x_1 (cost=0.01..1.01 rows=100 width=32) (actual time=0.002..0.002 rows=1 loops=1)

89. 0.012 0.022 ↑ 1.0 1 1

Aggregate (cost=10.79..10.80 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=1)

90. 0.000 0.010 ↓ 0.0 0 1

Append (cost=0.00..10.75 rows=2 width=84) (actual time=0.010..0.010 rows=0 loops=1)

91. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on catalog_file ctg_f (cost=0.00..0.00 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (((region_id = zai.region_id) OR (region_id IS NULL) OR (zai.region_id IS NULL)) AND (catalog_id = zai.catalog_id) AND (ninushi_id = zai.ninushi_id))
92. 0.007 0.007 ↓ 0.0 0 1

Index Scan using catalog_file_catalog_id_idx on catalog_file ctg_f_1 (cost=0.29..10.75 rows=1 width=96) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (catalog_id = zai.catalog_id)
  • Filter: (((region_id = zai.region_id) OR (region_id IS NULL) OR (zai.region_id IS NULL)) AND (ninushi_id = zai.ninushi_id))
Planning time : 14.758 ms
Execution time : 2,978.846 ms