explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Inmx

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 5,641.886 ↓ 0.0 0 1

Limit (cost=17,898,189,264.74..17,898,189,264.77 rows=10 width=91) (actual time=5,641.886..5,641.886 rows=0 loops=1)

2.          

CTE paths

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

4.          

CTE customer_paths

5. 0.544 597.608 ↑ 401.8 2,100 1

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

6. 0.637 0.637 ↑ 1.0 227 1

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

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

Nested Loop (cost=0.00..142,774.64 rows=84,363 width=61) (actual time=26.841..198.809 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.399 0.399 ↑ 3.3 700 3

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

9. 167.403 168.000 ↑ 1.0 2,196 2,100

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

10. 0.597 0.597 ↑ 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.597 rows=2,196 loops=1)

11.          

CTE customer_parents

12. 0.018 598.770 ↑ 4,014.0 1 1

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

  • Group Key: customer_paths_1.id, customer_paths_1.level
13. 0.011 598.752 ↑ 105,475.0 4 1

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

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

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

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

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

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

17.          

CTE ninushis_customers

18. 0.270 617.709 ↑ 16.6 110 1

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

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

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

20. 0.130 599.127 ↑ 14.8 61 1

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

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

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

22. 0.003 598.783 ↑ 66.7 3 1

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

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

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

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

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

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

26. 13.640 18.236 ↓ 2.0 1,832 1

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

  • Join Filter: (market.id = ANY (buyer.parent_ids))
  • Rows Removed by Join Filter: 132,124
27. 0.204 0.204 ↑ 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.204 rows=2,196 loops=1)

28. 3.700 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.136 0.692 ↑ 1.5 61 1

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

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

31. 0.002 0.450 ↑ 58.0 2 1

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

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

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

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

Seq Scan on customer market (cost=0.00..158.01 rows=231 width=8) (actual time=0.002..0.421 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.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.004 5,641.885 ↓ 0.0 0 1

Sort (cost=17,896,646,122.03..17,896,646,222.03 rows=40,000 width=91) (actual time=5,641.885..5,641.885 rows=0 loops=1)

  • Sort Key: t0.auction_date, t0.id
  • Sort Method: quicksort Memory: 25kB
39. 0.000 5,641.881 ↓ 0.0 0 1

GroupAggregate (cost=14,535,427,822.61..17,896,645,257.65 rows=40,000 width=91) (actual time=5,641.881..5,641.881 rows=0 loops=1)

  • Group Key: nnk.ninushi_nick, t0.id
40. 0.005 5,641.881 ↓ 0.0 0 1

Sort (cost=14,535,427,822.61..14,566,264,675.23 rows=12,334,741,046 width=102) (actual time=5,641.881..5,641.881 rows=0 loops=1)

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

Merge Join (cost=87,412.87..9,095,220,601.75 rows=12,334,741,046 width=102) (actual time=5,641.876..5,641.876 rows=0 loops=1)

  • Merge Cond: (nnk.ninushi_id = t0.ninushi_id)
42. 0.078 1.130 ↑ 327.0 1 1

Sort (cost=192.07..192.89 rows=327 width=36) (actual time=1.130..1.130 rows=1 loops=1)

  • Sort Key: nnk.ninushi_id
  • Sort Method: quicksort Memory: 46kB
43. 0.033 1.052 ↑ 1.0 325 1

Subquery Scan on nnk (cost=171.88..178.42 rows=327 width=36) (actual time=0.970..1.052 rows=325 loops=1)

44. 0.151 1.019 ↑ 1.0 325 1

HashAggregate (cost=171.88..175.15 rows=327 width=36) (actual time=0.969..1.019 rows=325 loops=1)

  • Group Key: ninushi.id, ninushi.nick
45. 0.021 0.868 ↑ 1.0 325 1

Append (cost=0.00..170.24 rows=327 width=36) (actual time=0.014..0.868 rows=325 loops=1)

46. 0.050 0.050 ↓ 1.0 98 1

Seq Scan on ninushi (cost=0.00..8.96 rows=96 width=16) (actual time=0.014..0.050 rows=98 loops=1)

47. 0.797 0.797 ↑ 1.0 227 1

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

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,969
48. 0.002 5,640.744 ↓ 0.0 0 1

Materialize (cost=87,220.79..8,929,123,360.86 rows=7,541,296,126 width=74) (actual time=5,640.744..5,640.744 rows=0 loops=1)

49. 83.700 5,640.742 ↓ 0.0 0 1

Nested Loop (cost=87,220.79..8,910,270,120.55 rows=7,541,296,126 width=74) (actual time=5,640.742..5,640.742 rows=0 loops=1)

50. 4,449.201 5,557.042 ↑ 58,615.5 128,657 1

Nested Loop Left Join (cost=87,220.26..765,670,304.47 rows=7,541,296,126 width=145) (actual time=905.444..5,557.042 rows=128,657 loops=1)

  • Filter: (CASE WHEN (t0.ninushi_id >= 20,000,000) THEN (hashed SubPlan 6) ELSE true END AND (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))
  • Rows Removed by Filter: 3,819
51. 1.407 922.376 ↑ 2,798.5 5,299 1

Nested Loop Left Join (cost=87,179.07..983,771.99 rows=14,829,275 width=130) (actual time=905.310..922.376 rows=5,299 loops=1)

52. 0.640 907.589 ↑ 92.2 892 1

Nested Loop (cost=87,179.07..141,880.30 rows=82,233 width=95) (actual time=905.297..907.589 rows=892 loops=1)

  • Join Filter: (paths.parent_id = ANY ((array_agg(ppbl.market_id))))
  • Rows Removed by Join Filter: 258
53. 0.439 901.199 ↑ 2.9 575 1

Merge Join (cost=87,179.07..87,205.30 rows=1,682 width=63) (actual time=900.593..901.199 rows=575 loops=1)

  • Merge Cond: (t0.ninushi_id = ninushis_customers.ninushi_id)
54. 0.263 900.703 ↑ 2.9 575 1

Sort (cost=87,126.22..87,130.43 rows=1,682 width=59) (actual time=900.541..900.703 rows=575 loops=1)

  • Sort Key: t0.ninushi_id
  • Sort Method: quicksort Memory: 105kB
55. 0.238 900.440 ↑ 2.9 575 1

Merge Join (cost=86,994.98..87,036.10 rows=1,682 width=59) (actual time=899.926..900.440 rows=575 loops=1)

  • Merge Cond: (ppbl.proposal_id = t0.id)
56. 1.086 899.368 ↓ 6.4 2,923 1

GroupAggregate (cost=86,519.89..86,530.11 rows=454 width=40) (actual time=898.167..899.368 rows=2,923 loops=1)

  • Group Key: ppbl.proposal_id, ppbl.customer_id
57. 1.498 898.282 ↓ 7.7 3,485 1

Sort (cost=86,519.89..86,521.03 rows=454 width=12) (actual time=898.158..898.282 rows=3,485 loops=1)

  • Sort Key: ppbl.proposal_id
  • Sort Method: quicksort Memory: 404kB
58. 0.864 896.784 ↓ 9.9 4,502 1

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

59. 1.538 895.920 ↓ 9.9 4,502 1

Hash Join (cost=59.51..79,343.60 rows=454 width=12) (actual time=617.760..895.920 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
60. 0.282 276.639 ↑ 1.1 4,502 1

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

61. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (customer_id = 482)
62. 0.006 0.006 ↑ 2.5 2 1

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

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

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 3,539,530
64. 0.015 617.743 ↑ 16.6 110 1

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

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

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

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

67. 0.000 0.004 ↑ 2,007.0 1 1

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

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

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

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

70. 0.194 0.834 ↓ 1.1 850 1

Sort (cost=475.09..476.94 rows=741 width=27) (actual time=0.801..0.834 rows=850 loops=1)

  • Sort Key: t0.id
  • Sort Method: quicksort Memory: 91kB
71. 0.037 0.640 ↓ 1.1 850 1

Append (cost=0.00..439.77 rows=741 width=27) (actual time=0.025..0.640 rows=850 loops=1)

72. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date) AND (active = 1) AND (publish = 1))
73. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on proposal t0_1 (cost=0.00..1.14 rows=1 width=46) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date) AND (active = 1) AND (publish = 1))
  • Rows Removed by Filter: 14
74. 0.593 0.593 ↓ 1.2 850 1

Index Scan using proposal_auction_date_idx on proposal t0_2 (cost=0.28..438.63 rows=739 width=27) (actual time=0.015..0.593 rows=850 loops=1)

  • Index Cond: ((auction_date >= '2019-01-10'::date) AND (auction_date <= '2019-04-10'::date))
  • Filter: ((active = 1) AND (publish = 1))
  • Rows Removed by Filter: 131
75. 0.020 0.057 ↑ 4.8 42 1

Sort (cost=52.84..53.34 rows=200 width=4) (actual time=0.050..0.057 rows=42 loops=1)

  • Sort Key: ninushis_customers.ninushi_id
  • Sort Method: quicksort Memory: 27kB
76. 0.027 0.037 ↑ 4.3 47 1

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

  • Group Key: ninushis_customers.ninushi_id
77. 0.010 0.010 ↑ 16.6 110 1

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

78. 5.750 5.750 ↑ 500.0 2 575

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

79. 1.784 13.380 ↑ 1.3 6 892

Append (cost=0.00..10.16 rows=8 width=39) (actual time=0.006..0.015 rows=6 loops=892)

80. 0.000 0.000 ↓ 0.0 0 892

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

  • Filter: (proposal_id = t0.id)
81. 0.892 0.892 ↓ 0.0 0 892

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_1 (cost=0.13..0.17 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=892)

  • Index Cond: (proposal_id = t0.id)
82. 10.704 10.704 ↑ 1.0 6 892

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_2 (cost=0.29..9.99 rows=6 width=39) (actual time=0.003..0.012 rows=6 loops=892)

  • Index Cond: (proposal_id = t0.id)
83. 21.196 185.465 ↑ 3.1 25 5,299

Append (cost=0.00..10.23 rows=78 width=435) (actual time=0.005..0.035 rows=25 loops=5,299)

84. 0.000 0.000 ↓ 0.0 0 5,299

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

  • Filter: (proposal_panel_id = ppl.id)
85. 5.299 5.299 ↓ 0.0 0 5,299

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_1 (cost=0.27..0.98 rows=40 width=123) (actual time=0.001..0.001 rows=0 loops=5,299)

  • Index Cond: (proposal_panel_id = ppl.id)
86. 158.970 158.970 ↑ 1.5 25 5,299

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.030 rows=25 loops=5,299)

  • Index Cond: (proposal_panel_id = ppl.id)
87.          

SubPlan (for Nested Loop Left Join)

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

89. 0.000 0.000 ↓ 0.0 0 128,657

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

  • 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())
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 : 3.809 ms
Execution time : 5,642.307 ms