explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QL6U

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 918.310 ↓ 0.0 0 1

Limit (cost=4,329,255,600.73..4,329,255,600.76 rows=10 width=91) (actual time=918.310..918.310 rows=0 loops=1)

2.          

CTE paths

3. 9.759 9.759 ↑ 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=9.755..9.759 rows=2 loops=1)

4.          

CTE customer_paths

5. 0.556 610.677 ↑ 401.8 2,100 1

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

6. 0.653 0.653 ↑ 1.0 227 1

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

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

Nested Loop (cost=0.00..142,774.64 rows=84,363 width=61) (actual time=27.082..203.156 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.540 0.540 ↑ 3.3 700 3

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

9. 171.529 172.200 ↑ 1.0 2,196 2,100

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

10. 0.671 0.671 ↑ 1.0 2,196 1

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

11.          

CTE customer_parents

12. 0.014 611.465 ↑ 4,014.0 1 1

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

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

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

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

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

15. 611.421 611.421 ↑ 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=73.057..611.421 rows=2 loops=1)

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

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

17.          

CTE ninushis_customers

18. 0.271 629.776 ↑ 16.6 110 1

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

  • Group Key: ninushi_customer.ninushi_id, ninushi_customer.customer_id
19. 0.076 629.505 ↓ 1.0 1,893 1

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

20. 0.158 611.804 ↑ 14.8 61 1

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

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

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

22. 0.007 611.481 ↑ 66.7 3 1

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

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

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

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

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

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

26. 13.045 17.625 ↓ 2.0 1,832 1

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

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

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

28. 3.708 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.132 0.684 ↑ 1.5 61 1

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

  • Hash Cond: (nnc.customer_id = market.id)
30. 0.114 0.114 ↓ 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.114 rows=1,809 loops=1)

31. 0.001 0.438 ↑ 58.0 2 1

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

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

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

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

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

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

Hash (cost=7,136.89..7,136.89 rows=200 width=4) (actual time=0.006..0.006 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.002 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.000 0.000 ↑ 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.000 rows=1 loops=1)

38. 0.005 918.309 ↓ 0.0 0 1

Sort (cost=4,327,712,458.02..4,327,712,558.02 rows=40,000 width=91) (actual time=918.309..918.309 rows=0 loops=1)

  • Sort Key: t0.auction_date, t0.id
  • Sort Method: quicksort Memory: 25kB
39. 0.001 918.304 ↓ 0.0 0 1

GroupAggregate (cost=2,668,775,230.19..4,327,711,593.63 rows=40,000 width=91) (actual time=918.304..918.304 rows=0 loops=1)

  • Group Key: nnk.ninushi_nick, t0.id
40. 0.003 918.303 ↓ 0.0 0 1

Sort (cost=2,668,775,230.19..2,683,994,825.26 rows=6,087,838,031 width=102) (actual time=918.303..918.303 rows=0 loops=1)

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

Nested Loop (cost=85,612.32..14,758,398.46 rows=6,087,838,031 width=102) (actual time=918.300..918.300 rows=0 loops=1)

42. 0.005 918.146 ↑ 5,475.5 2 1

Nested Loop (cost=85,336.10..90,963.94 rows=10,951 width=68) (actual time=918.145..918.146 rows=2 loops=1)

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

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

44. 0.001 908.374 ↑ 112.0 2 2

Materialize (cost=85,336.10..85,344.50 rows=224 width=36) (actual time=454.187..454.187 rows=2 loops=2)

45. 0.002 908.373 ↑ 112.0 2 1

Subquery Scan on ppb (cost=85,336.10..85,343.38 rows=224 width=36) (actual time=908.371..908.373 rows=2 loops=1)

46. 0.008 908.371 ↑ 112.0 2 1

GroupAggregate (cost=85,336.10..85,341.14 rows=224 width=40) (actual time=908.370..908.371 rows=2 loops=1)

  • Group Key: ppbl.proposal_id, ppbl.customer_id
47. 0.008 908.363 ↑ 112.0 2 1

Sort (cost=85,336.10..85,336.66 rows=224 width=12) (actual time=908.363..908.363 rows=2 loops=1)

  • Sort Key: ppbl.proposal_id
  • Sort Method: quicksort Memory: 25kB
48. 0.003 908.355 ↑ 112.0 2 1

Nested Loop Semi Join (cost=0.00..85,327.36 rows=224 width=12) (actual time=629.781..908.355 rows=2 loops=1)

49. 0.648 908.348 ↑ 112.0 2 1

Nested Loop (cost=0.00..78,176.85 rows=224 width=12) (actual time=629.775..908.348 rows=2 loops=1)

  • Join Filter: (ppbl.ninushi_id = nnscs.ninushi_id)
  • Rows Removed by Join Filter: 9,002
50. 629.796 629.796 ↑ 4.5 2 1

CTE Scan on ninushis_customers nnscs (cost=0.00..41.20 rows=9 width=8) (actual time=629.767..629.796 rows=2 loops=1)

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 108
51. 0.955 277.904 ↑ 1.1 4,502 2

Materialize (cost=0.00..77,477.12 rows=4,970 width=16) (actual time=0.003..138.952 rows=4,502 loops=2)

52. 0.290 276.949 ↑ 1.1 4,502 1

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

53. 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)
54. 0.002 0.002 ↑ 2.5 2 1

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

  • Filter: (customer_id = 482)
55. 276.654 276.654 ↑ 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.038..276.654 rows=4,500 loops=1)

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 3,539,530
56. 0.001 0.004 ↑ 2,007.0 1 2

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

57. 0.001 0.003 ↑ 2,007.0 1 1

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

  • Filter: ("ANY_subquery".unnest = 482)
  • Rows Removed by Filter: 2
58. 0.002 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)

59. 0.000 0.000 ↑ 4,014.0 1 1

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

60. 0.002 0.152 ↓ 0.0 0 2

Hash Join (cost=276.22..1,593.06 rows=2,250 width=526) (actual time=0.076..0.076 rows=0 loops=2)

  • Hash Cond: (t0.ninushi_id = nnk.ninushi_id)
61. 0.006 0.150 ↓ 0.0 0 2

Hash Join (cost=93.72..1,327.86 rows=1,376 width=498) (actual time=0.075..0.075 rows=0 loops=2)

  • Hash Cond: (t0.ninushi_id = ninushis_customers.ninushi_id)
62. 0.000 0.112 ↓ 0.0 0 2

Hash Join (cost=46.02..1,262.85 rows=1,376 width=494) (actual time=0.056..0.056 rows=0 loops=2)

  • 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. 0.002 0.112 ↓ 0.0 0 2

Nested Loop (cost=0.53..1,184.40 rows=8,139 width=467) (actual time=0.056..0.056 rows=0 loops=2)

64. 0.002 0.016 ↑ 8.0 1 2

Append (cost=0.00..18.15 rows=8 width=39) (actual time=0.006..0.008 rows=1 loops=2)

65. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on proposal_panel ppl (cost=0.00..0.00 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: (ppb.proposal_id = proposal_id)
66. 0.008 0.008 ↑ 1.0 1 2

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_1 (cost=0.13..0.20 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (proposal_id = ppb.proposal_id)
67. 0.004 0.004 ↓ 0.0 0 2

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_2 (cost=0.29..17.95 rows=6 width=39) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: (proposal_id = ppb.proposal_id)
68. 0.036 0.094 ↓ 0.0 0 2

Nested Loop (cost=0.53..145.50 rows=28 width=432) (actual time=0.047..0.047 rows=0 loops=2)

  • 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. 0.008 0.058 ↑ 1.1 80 2

Append (cost=0.00..10.35 rows=85 width=435) (actual time=0.005..0.029 rows=80 loops=2)

70. 0.000 0.000 ↓ 0.0 0 2

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

  • Filter: (ppl.id = proposal_panel_id)
71. 0.046 0.046 ↓ 1.7 80 2

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.004..0.023 rows=80 loops=2)

  • Index Cond: (proposal_panel_id = ppl.id)
72. 0.004 0.004 ↓ 0.0 0 2

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.002..0.002 rows=0 loops=2)

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

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

  • 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.000 ↓ 0.0 0

Hash (cost=4.26..4.26 rows=3 width=27) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..4.26 rows=3 width=27) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Seq Scan on proposal t0 (cost=0.00..0.00 rows=1 width=46) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Index Scan using proposal_pkey on proposal t0_1 (cost=0.13..0.21 rows=1 width=46) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Index Scan using proposal_pkey on proposal t0_2 (cost=0.28..4.04 rows=1 width=27) (never executed)

  • 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))
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.004 0.032 ↑ 4.3 47 1

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

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

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

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

CTE Scan on ninushis_customers (cost=0.00..36.62 rows=1,831 width=4) (actual time=0.000..0.007 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 : 16.249 ms
Execution time : 918.930 ms