explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MbWK

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,041.084 ↓ 0.0 0 1

Limit (cost=8,835,584,939.50..8,835,584,939.52 rows=10 width=91) (actual time=2,041.084..2,041.084 rows=0 loops=1)

2.          

CTE paths

3. 12.093 12.093 ↑ 500.0 2 1

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

4.          

CTE customer_paths

5. 0.685 645.163 ↑ 401.8 2,100 1

Recursive Union (cost=0.00..1,444,781.68 rows=843,861 width=61) (actual time=0.006..645.163 rows=2,100 loops=1)

6. 0.651 0.651 ↑ 1.0 227 1

Seq Scan on customer customer_1 (cost=0.00..158.01 rows=231 width=61) (actual time=0.005..0.651 rows=227 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,969
7. 464.706 643.827 ↑ 135.2 624 3

Nested Loop (cost=0.00..142,774.64 rows=84,363 width=61) (actual time=29.809..214.609 rows=624 loops=3)

  • Join Filter: ((customer_paths.level < customer_2.level) AND (customer_paths.id = ANY (customer_2.parent_ids)))
  • Rows Removed by Join Filter: 1,536,576
8. 0.621 0.621 ↑ 3.3 700 3

WorkTable Scan on customer_paths (cost=0.00..46.20 rows=2,310 width=38) (actual time=0.000..0.207 rows=700 loops=3)

9. 177.904 178.500 ↑ 1.0 2,196 2,100

Materialize (cost=0.00..163.62 rows=2,241 width=29) (actual time=0.000..0.085 rows=2,196 loops=2,100)

10. 0.596 0.596 ↑ 1.0 2,196 1

Seq Scan on customer customer_2 (cost=0.00..152.41 rows=2,241 width=29) (actual time=0.003..0.596 rows=2,196 loops=1)

11.          

CTE customer_parents

12. 0.023 646.347 ↑ 4,014.0 1 1

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

  • Group Key: customer_paths_1.id, customer_paths_1.level
13. 0.008 646.324 ↑ 105,475.0 4 1

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

  • Sort Key: customer_paths_1.level
  • Sort Method: quicksort Memory: 25kB
14. 0.009 646.316 ↑ 105,475.0 4 1

Nested Loop (cost=0.00..27,424.87 rows=421,900 width=10) (actual time=84.821..646.316 rows=4 loops=1)

15. 646.287 646.287 ↑ 2,109.5 2 1

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

  • Filter: (id = 482)
  • Rows Removed by Filter: 2,098
16. 0.020 0.020 ↑ 50.0 2 2

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

17.          

CTE ninushis_customers

18. 0.277 665.944 ↑ 16.6 110 1

HashAggregate (cost=19,993.16..20,011.47 rows=1,831 width=8) (actual time=665.927..665.944 rows=110 loops=1)

  • Group Key: ninushi_customer.ninushi_id, ninushi_customer.customer_id
19. 0.080 665.667 ↓ 1.0 1,893 1

Append (cost=7,139.39..19,984.01 rows=1,831 width=8) (actual time=646.397..665.667 rows=1,893 loops=1)

20. 0.130 646.719 ↑ 14.8 61 1

Hash Join (cost=7,139.39..7,224.23 rows=903 width=8) (actual time=646.396..646.719 rows=61 loops=1)

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

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

22. 0.003 646.359 ↑ 66.7 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.004 646.356 ↑ 66.7 3 1

HashAggregate (cost=7,134.89..7,136.89 rows=200 width=4) (actual time=646.355..646.356 rows=3 loops=1)

  • Group Key: unnest(customer_parents_1.parents_and_self)
24. 0.003 646.352 ↑ 133,800.0 3 1

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

25. 646.349 646.349 ↑ 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=646.348..646.349 rows=1 loops=1)

26. 14.265 18.868 ↓ 2.0 1,832 1

Nested Loop (cost=7,300.73..12,741.46 rows=928 width=8) (actual time=3.946..18.868 rows=1,832 loops=1)

  • Join Filter: (market.id = ANY (buyer.parent_ids))
  • Rows Removed by Join Filter: 132,124
27. 0.211 0.211 ↑ 1.0 2,196 1

Seq Scan on customer buyer (cost=0.00..152.41 rows=2,241 width=27) (actual time=0.004..0.211 rows=2,196 loops=1)

28. 3.721 4.392 ↑ 1.5 61 2,196

Materialize (cost=7,300.73..7,378.96 rows=93 width=8) (actual time=0.000..0.002 rows=61 loops=2,196)

29. 0.128 0.671 ↑ 1.5 61 1

Hash Join (cost=7,300.73..7,378.50 rows=93 width=8) (actual time=0.459..0.671 rows=61 loops=1)

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

Seq Scan on ninushi_customer nnc (cost=0.00..70.06 rows=1,806 width=4) (actual time=0.002..0.102 rows=1,809 loops=1)

31. 0.001 0.441 ↑ 58.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.015 0.440 ↑ 58.0 2 1

Hash Join (cost=7,139.39..7,299.28 rows=116 width=12) (actual time=0.077..0.440 rows=2 loops=1)

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

Seq Scan on customer market (cost=0.00..158.01 rows=231 width=8) (actual time=0.002..0.418 rows=227 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,969
34. 0.002 0.007 ↑ 66.7 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.003 0.005 ↑ 66.7 3 1

HashAggregate (cost=7,134.89..7,136.89 rows=200 width=4) (actual time=0.004..0.005 rows=3 loops=1)

  • Group Key: unnest(customer_parents_2.parents_and_self)
36. 0.001 0.002 ↑ 133,800.0 3 1

ProjectSet (cost=0.00..2,117.39 rows=401,400 width=4) (actual time=0.002..0.002 rows=3 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.000..0.001 rows=1 loops=1)

38. 0.014 2,041.081 ↓ 0.0 0 1

Sort (cost=8,834,041,796.79..8,834,041,896.79 rows=40,000 width=91) (actual time=2,041.081..2,041.081 rows=0 loops=1)

  • Sort Key: t0.auction_date, t0.id
  • Sort Method: quicksort Memory: 25kB
39. 0.002 2,041.067 ↓ 0.0 0 1

GroupAggregate (cost=5,471,732,927.29..8,834,040,932.40 rows=40,000 width=91) (actual time=2,041.067..2,041.067 rows=0 loops=1)

  • Group Key: nnk.ninushi_nick, t0.id
40. 0.010 2,041.065 ↓ 0.0 0 1

Sort (cost=5,471,732,927.29..5,502,579,785.14 rows=12,338,743,138 width=102) (actual time=2,041.065..2,041.065 rows=0 loops=1)

  • Sort Key: nnk.ninushi_nick, t0.id
  • Sort Method: quicksort Memory: 25kB
41. 2.422 2,041.055 ↓ 0.0 0 1

Nested Loop (cost=86,795.38..29,731,714.45 rows=12,338,743,138 width=102) (actual time=2,041.055..2,041.055 rows=0 loops=1)

42. 1.789 1,007.675 ↑ 4.9 4,502 1

Nested Loop (cost=86,519.89..97,905.78 rows=22,196 width=68) (actual time=999.925..1,007.675 rows=4,502 loops=1)

  • Join Filter: (paths.parent_id = ANY (ppb.market_ids))
  • Rows Removed by Join Filter: 2,708
43. 12.108 12.108 ↑ 500.0 2 1

CTE Scan on paths (cost=0.00..20.00 rows=1,000 width=68) (actual time=12.098..12.108 rows=2 loops=1)

44. 1.145 993.778 ↓ 7.9 3,605 2

Materialize (cost=86,519.89..86,536.92 rows=454 width=36) (actual time=493.912..496.889 rows=3,605 loops=2)

45. 0.603 992.633 ↓ 7.9 3,605 1

Subquery Scan on ppb (cost=86,519.89..86,534.65 rows=454 width=36) (actual time=987.822..992.633 rows=3,605 loops=1)

46. 3.654 992.030 ↓ 7.9 3,605 1

GroupAggregate (cost=86,519.89..86,530.11 rows=454 width=40) (actual time=987.821..992.030 rows=3,605 loops=1)

  • Group Key: ppbl.proposal_id, ppbl.customer_id
47. 2.187 988.376 ↓ 9.9 4,502 1

Sort (cost=86,519.89..86,521.03 rows=454 width=12) (actual time=987.812..988.376 rows=4,502 loops=1)

  • Sort Key: ppbl.proposal_id
  • Sort Method: quicksort Memory: 404kB
48. 1.146 986.189 ↓ 9.9 4,502 1

Nested Loop Semi Join (cost=59.51..86,499.85 rows=454 width=12) (actual time=666.013..986.189 rows=4,502 loops=1)

49. 2.017 985.043 ↓ 9.9 4,502 1

Hash Join (cost=59.51..79,343.60 rows=454 width=12) (actual time=666.002..985.043 rows=4,502 loops=1)

  • Hash Cond: (ppbl.ninushi_id = nnscs.ninushi_id)
  • Join Filter: ((nnscs.customer_id = ppbl.market_id) OR (nnscs.customer_id = ppbl.customer_id))
  • Rows Removed by Join Filter: 4,081
50. 0.342 317.050 ↑ 1.1 4,502 1

Append (cost=0.00..77,452.27 rows=4,970 width=16) (actual time=0.017..317.050 rows=4,502 loops=1)

51. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on proposal_publish ppbl (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (customer_id = 482)
52. 0.014 0.014 ↑ 2.5 2 1

Seq Scan on proposal_publish ppbl_1 (cost=0.00..23.38 rows=5 width=16) (actual time=0.013..0.014 rows=2 loops=1)

  • Filter: (customer_id = 482)
53. 316.691 316.691 ↑ 1.1 4,500 1

Seq Scan on proposal_publish ppbl_2 (cost=0.00..77,428.90 rows=4,964 width=16) (actual time=0.053..316.691 rows=4,500 loops=1)

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 3,539,530
54. 0.013 665.976 ↑ 16.6 110 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 21kB
55. 665.963 665.963 ↑ 16.6 110 1

CTE Scan on ninushis_customers nnscs (cost=0.00..36.62 rows=1,831 width=8) (actual time=665.929..665.963 rows=110 loops=1)

56. 0.000 0.000 ↑ 2,007.0 1 4,502

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

57. 0.001 0.005 ↑ 2,007.0 1 1

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

  • Filter: ("ANY_subquery".unnest = 482)
  • Rows Removed by Filter: 2
58. 0.003 0.004 ↑ 133,800.0 3 1

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

59. 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)

60. 0.000 1,030.958 ↓ 0.0 0 4,502

Hash Join (cost=275.49..1,588.08 rows=2,250 width=526) (actual time=0.229..0.229 rows=0 loops=4,502)

  • Hash Cond: (t0.ninushi_id = nnk.ninushi_id)
61. 0.000 1,030.958 ↓ 0.0 0 4,502

Hash Join (cost=92.99..1,322.87 rows=1,376 width=498) (actual time=0.229..0.229 rows=0 loops=4,502)

  • Hash Cond: (t0.ninushi_id = ninushis_customers.ninushi_id)
62. 4.925 1,030.958 ↓ 0.0 0 4,502

Hash Join (cost=45.29..1,257.86 rows=1,376 width=494) (actual time=0.229..0.229 rows=0 loops=4,502)

  • Hash Cond: (ppl.proposal_id = t0.id)
  • Join Filter: CASE WHEN (t0.ninushi_id >= 20,000,000) THEN (hashed SubPlan 6) ELSE true END
63. 21.117 1,026.000 ↓ 0.0 0 4,500

Nested Loop (cost=0.53..1,180.14 rows=8,139 width=467) (actual time=0.228..0.228 rows=0 loops=4,500)

64. 4.500 49.500 ↑ 1.3 6 4,500

Append (cost=0.00..13.89 rows=8 width=39) (actual time=0.004..0.011 rows=6 loops=4,500)

65. 0.000 0.000 ↓ 0.0 0 4,500

Seq Scan on proposal_panel ppl (cost=0.00..0.00 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=4,500)

  • Filter: (ppb.proposal_id = proposal_id)
66. 4.500 4.500 ↓ 0.0 0 4,500

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_1 (cost=0.13..0.18 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=4,500)

  • Index Cond: (proposal_id = ppb.proposal_id)
67. 40.500 40.500 ↑ 1.0 6 4,500

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_2 (cost=0.29..13.72 rows=6 width=39) (actual time=0.002..0.009 rows=6 loops=4,500)

  • Index Cond: (proposal_id = ppb.proposal_id)
68. 289.510 955.383 ↓ 0.0 0 28,951

Nested Loop (cost=0.53..145.50 rows=28 width=432) (actual time=0.033..0.033 rows=0 loops=28,951)

  • Join Filter: (COALESCE(COALESCE(extract_custom_prices(pd.custom_prices, jsonb_array_to_text_array((paths.root_level_paths_deadline_by_nns_id -> (pd.original_ninushi_id)::text)), paths.nested_paths_deadline, 0)), pd.unit_price) > '0'::double precision)
69. 57.902 665.873 ↑ 3.4 25 28,951

Append (cost=0.00..10.35 rows=85 width=435) (actual time=0.004..0.023 rows=25 loops=28,951)

70. 0.000 0.000 ↓ 0.0 0 28,951

Seq Scan on proposal_detail pd (cost=0.00..0.00 rows=1 width=88) (actual time=0.000..0.000 rows=0 loops=28,951)

  • Filter: (ppl.id = proposal_panel_id)
71. 28.951 28.951 ↓ 0.0 0 28,951

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_1 (cost=0.28..1.10 rows=47 width=89) (actual time=0.001..0.001 rows=0 loops=28,951)

  • Index Cond: (proposal_panel_id = ppl.id)
72. 579.020 579.020 ↑ 1.5 25 28,951

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_2 (cost=0.42..9.25 rows=37 width=435) (actual time=0.003..0.020 rows=25 loops=28,951)

  • Index Cond: (proposal_panel_id = ppl.id)
73. 0.000 0.000 ↓ 0.0 0 709,536

Result (cost=0.53..1.06 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=709,536)

  • One-Time Filter: (COALESCE(COALESCE(CASE WHEN ((pd.custom_deadlines IS NULL) OR ((pd.custom_deadlines)::text = '[]'::text) OR ((pd.custom_deadlines)::text = '{}'::text)) THEN NULL::timestamp without time zone ELSE extract_custom_deadlines(pd.custom_deadlines, jsonb_array_to_text_array((paths.root_level_paths_deadline_by_nns_id -> (pd.original_ninushi_id)::text)), paths.nested_paths_deadline, 0) END), pd.deadline_time) >= now())
74. 0.000 0.033 ↓ 0.0 0 3

Hash (cost=3.53..3.53 rows=3 width=27) (actual time=0.011..0.011 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
75. 0.000 0.033 ↓ 0.0 0 3

Append (cost=0.00..3.53 rows=3 width=27) (actual time=0.011..0.011 rows=0 loops=3)

76. 0.003 0.003 ↓ 0.0 0 3

Seq Scan on proposal t0 (cost=0.00..0.00 rows=1 width=46) (actual time=0.001..0.001 rows=0 loops=3)

  • Filter: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date) AND (active = 1) AND (publish = 1) AND (ppb.proposal_id = id))
77. 0.015 0.015 ↓ 0.0 0 3

Index Scan using proposal_pkey on proposal t0_1 (cost=0.13..0.19 rows=1 width=46) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: (id = ppb.proposal_id)
  • Filter: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date) AND (active = 1) AND (publish = 1))
78. 0.015 0.015 ↓ 0.0 0 3

Index Scan using proposal_pkey on proposal t0_2 (cost=0.28..3.34 rows=1 width=27) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: (id = ppb.proposal_id)
  • Filter: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date) AND (active = 1) AND (publish = 1))
  • Rows Removed by Filter: 1
79.          

SubPlan (for Hash Join)

80. 0.000 0.000 ↓ 0.0 0

CTE Scan on ninushis_customers ninushis_customers_1 (cost=0.00..36.62 rows=1,831 width=8) (never executed)

81. 0.005 0.053 ↑ 4.3 47 1

Hash (cost=45.20..45.20 rows=200 width=4) (actual time=0.053..0.053 rows=47 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
82. 0.039 0.048 ↑ 4.3 47 1

HashAggregate (cost=41.20..43.20 rows=200 width=4) (actual time=0.043..0.048 rows=47 loops=1)

  • Group Key: ninushis_customers.ninushi_id
83. 0.009 0.009 ↑ 16.6 110 1

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

84. 0.000 0.000 ↓ 0.0 0

Hash (cost=178.42..178.42 rows=327 width=36) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Subquery Scan on nnk (cost=171.88..178.42 rows=327 width=36) (never executed)

86. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=171.88..175.15 rows=327 width=36) (never executed)

  • Group Key: ninushi.id, ninushi.nick
87. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..170.24 rows=327 width=36) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Seq Scan on ninushi (cost=0.00..8.96 rows=96 width=16) (never executed)

89. 0.000 0.000 ↓ 0.0 0

Seq Scan on customer (cost=0.00..158.01 rows=231 width=18) (never executed)

  • Filter: (level = 1)
90.          

SubPlan (for GroupAggregate)

91. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.26 rows=1 width=32) (never executed)

Planning time : 8.502 ms
Execution time : 2,041.982 ms