explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GFwS

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 16,159.205 ↑ 1.0 10 1

Limit (cost=1,660,926.27..1,660,926.29 rows=10 width=91) (actual time=16,159.203..16,159.205 rows=10 loops=1)

2.          

CTE paths

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

4.          

CTE customer_paths

5. 0.620 698.342 ↑ 380.8 2,083 1

Recursive Union (cost=0.00..1,357,928.93 rows=793,104 width=61) (actual time=0.006..698.342 rows=2,083 loops=1)

6. 2.274 2.274 ↑ 1.0 224 1

Seq Scan on customer customer_1 (cost=0.00..153.15 rows=224 width=61) (actual time=0.005..2.274 rows=224 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,949
7. 484.330 695.448 ↑ 127.9 620 3

Nested Loop (cost=0.00..134,191.37 rows=79,288 width=61) (actual time=28.581..231.816 rows=620 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,508,167
8. 0.735 0.735 ↑ 3.2 694 3

WorkTable Scan on customer_paths (cost=0.00..44.80 rows=2,240 width=38) (actual time=0.002..0.245 rows=694 loops=3)

9. 208.609 210.383 ↓ 1.0 2,173 2,083

Materialize (cost=0.00..158.58 rows=2,172 width=29) (actual time=0.000..0.101 rows=2,173 loops=2,083)

10. 1.774 1.774 ↓ 1.0 2,173 1

Seq Scan on customer customer_2 (cost=0.00..147.72 rows=2,172 width=29) (actual time=0.018..1.774 rows=2,173 loops=1)

11.          

CTE customer_parents

12. 0.023 699.217 ↑ 3,785.0 1 1

GroupAggregate (cost=69,434.84..73,448.15 rows=3,785 width=38) (actual time=699.217..699.217 rows=1 loops=1)

  • Group Key: customer_paths_1.id, customer_paths_1.level
13. 0.008 699.194 ↑ 99,150.0 4 1

Sort (cost=69,434.84..70,426.34 rows=396,600 width=10) (actual time=699.193..699.194 rows=4 loops=1)

  • Sort Key: customer_paths_1.level
  • Sort Method: quicksort Memory: 25kB
14. 0.005 699.186 ↑ 99,150.0 4 1

Nested Loop (cost=0.00..25,776.84 rows=396,600 width=10) (actual time=30.520..699.186 rows=4 loops=1)

15. 699.161 699.161 ↑ 1,983.0 2 1

CTE Scan on customer_paths customer_paths_1 (cost=0.00..17,844.84 rows=3,966 width=38) (actual time=30.508..699.161 rows=2 loops=1)

  • Filter: (id = 1,677)
  • Rows Removed by Filter: 2,081
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.009..0.010 rows=2 loops=2)

17.          

CTE ninushis_customers

18. 0.531 723.860 ↑ 16.3 120 1

HashAggregate (cost=19,393.34..19,412.93 rows=1,959 width=8) (actual time=723.828..723.860 rows=120 loops=1)

  • Group Key: ninushi_customer.ninushi_id, ninushi_customer.customer_id
19. 0.134 723.329 ↓ 1.0 2,028 1

Append (cost=6,732.34..19,383.55 rows=1,959 width=8) (actual time=699.262..723.329 rows=2,028 loops=1)

20. 0.279 699.727 ↑ 21.0 46 1

Hash Join (cost=6,732.34..6,807.48 rows=966 width=8) (actual time=699.262..699.727 rows=46 loops=1)

  • Hash Cond: (ninushi_customer.customer_id = (unnest(customer_parents_1.parents_and_self)))
21. 0.216 0.216 ↑ 1.1 1,838 1

Seq Scan on ninushi_customer (cost=0.00..59.32 rows=1,932 width=8) (actual time=0.012..0.216 rows=1,838 loops=1)

22. 0.002 699.232 ↑ 66.7 3 1

Hash (cost=6,729.84..6,729.84 rows=200 width=4) (actual time=699.232..699.232 rows=3 loops=1)

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

HashAggregate (cost=6,727.84..6,729.84 rows=200 width=4) (actual time=699.228..699.230 rows=3 loops=1)

  • Group Key: unnest(customer_parents_1.parents_and_self)
24. 0.005 699.224 ↑ 126,166.7 3 1

ProjectSet (cost=0.00..1,996.59 rows=378,500 width=4) (actual time=699.223..699.224 rows=3 loops=1)

25. 699.219 699.219 ↑ 3,785.0 1 1

CTE Scan on customer_parents customer_parents_1 (cost=0.00..75.70 rows=3,785 width=32) (actual time=699.219..699.219 rows=1 loops=1)

26. 16.640 23.468 ↓ 2.0 1,982 1

Nested Loop (cost=6,815.81..12,556.48 rows=993 width=8) (actual time=5.990..23.468 rows=1,982 loops=1)

  • Join Filter: (market.id = ANY (buyer.parent_ids))
  • Rows Removed by Join Filter: 97,976
27. 0.309 0.309 ↓ 1.0 2,173 1

Seq Scan on customer buyer (cost=0.00..147.72 rows=2,172 width=27) (actual time=0.007..0.309 rows=2,173 loops=1)

28. 5.146 6.519 ↑ 2.2 46 2,173

Materialize (cost=6,815.81..6,979.01 rows=100 width=8) (actual time=0.000..0.003 rows=46 loops=2,173)

29. 0.018 1.373 ↑ 2.2 46 1

Hash Join (cost=6,815.81..6,978.51 rows=100 width=8) (actual time=0.739..1.373 rows=46 loops=1)

  • Hash Cond: (market.id = nnc.customer_id)
30. 0.028 0.707 ↑ 56.0 2 1

Hash Join (cost=6,732.34..6,887.32 rows=112 width=12) (actual time=0.084..0.707 rows=2 loops=1)

  • Hash Cond: (market.id = (unnest(customer_parents_2.parents_and_self)))
31. 0.669 0.669 ↑ 1.0 224 1

Seq Scan on customer market (cost=0.00..153.15 rows=224 width=8) (actual time=0.003..0.669 rows=224 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,949
32. 0.001 0.010 ↑ 66.7 3 1

Hash (cost=6,729.84..6,729.84 rows=200 width=4) (actual time=0.010..0.010 rows=3 loops=1)

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

HashAggregate (cost=6,727.84..6,729.84 rows=200 width=4) (actual time=0.008..0.009 rows=3 loops=1)

  • Group Key: unnest(customer_parents_2.parents_and_self)
34. 0.003 0.004 ↑ 126,166.7 3 1

ProjectSet (cost=0.00..1,996.59 rows=378,500 width=4) (actual time=0.003..0.004 rows=3 loops=1)

35. 0.001 0.001 ↑ 3,785.0 1 1

CTE Scan on customer_parents customer_parents_2 (cost=0.00..75.70 rows=3,785 width=32) (actual time=0.001..0.001 rows=1 loops=1)

36. 0.314 0.648 ↑ 1.1 1,838 1

Hash (cost=59.32..59.32 rows=1,932 width=4) (actual time=0.648..0.648 rows=1,838 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 81kB
37. 0.334 0.334 ↑ 1.1 1,838 1

Seq Scan on ninushi_customer nnc (cost=0.00..59.32 rows=1,932 width=4) (actual time=0.005..0.334 rows=1,838 loops=1)

38. 0.320 16,159.203 ↑ 3,311.5 10 1

Sort (cost=210,113.50..210,196.29 rows=33,115 width=91) (actual time=16,159.202..16,159.203 rows=10 loops=1)

  • Sort Key: t0.auction_date, t0.id
  • Sort Method: top-N heapsort Memory: 43kB
39. 268.843 16,158.883 ↑ 51.3 646 1

GroupAggregate (cost=199,960.12..209,397.90 rows=33,115 width=91) (actual time=15,416.947..16,158.883 rows=646 loops=1)

  • Group Key: nnk.ninushi_nick, t0.id
40. 5,277.304 15,471.924 ↓ 4.2 139,372 1

Sort (cost=199,960.12..200,042.91 rows=33,115 width=102) (actual time=15,415.052..15,471.924 rows=139,372 loops=1)

  • Sort Key: nnk.ninushi_nick, t0.id
  • Sort Method: external merge Disk: 13,664kB
41. 68.107 10,194.620 ↓ 4.2 139,372 1

Hash Join (cost=92,975.25..195,660.98 rows=33,115 width=102) (actual time=1,120.154..10,194.620 rows=139,372 loops=1)

  • Hash Cond: (nnk.ninushi_id = ninushis_customers.ninushi_id)
42. 75.316 9,402.582 ↓ 4.2 139,372 1

Hash Join (cost=92,924.67..195,193.80 rows=33,115 width=106) (actual time=396.219..9,402.582 rows=139,372 loops=1)

  • Hash Cond: (t0.ninushi_id = nnk.ninushi_id)
43. 37.048 9,326.339 ↓ 6.7 139,372 1

Nested Loop (cost=92,747.36..193,805.72 rows=20,697 width=70) (actual time=395.281..9,326.339 rows=139,372 loops=1)

44. 93.887 9,149.919 ↓ 6.7 139,372 1

Hash Join (cost=92,747.36..182,370.63 rows=20,697 width=141) (actual time=395.277..9,149.919 rows=139,372 loops=1)

  • Hash Cond: (pd.catalog_id = ctg.id)
45. 8,249.311 9,042.447 ↓ 6.7 139,372 1

Hash Join (cost=91,392.29..180,730.97 rows=20,697 width=145) (actual time=381.664..9,042.447 rows=139,372 loops=1)

  • Hash Cond: (ppl.proposal_id = ppbl.proposal_id)
  • 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)
  • Rows Removed by Join Filter: 543
46. 101.706 432.620 ↓ 1.1 86,226 1

Hash Join (cost=3,031.93..55,095.26 rows=78,211 width=478) (actual time=20.750..432.620 rows=86,226 loops=1)

  • Hash Cond: (pd.proposal_panel_id = ppl.id)
  • Join Filter: CASE WHEN (t0.ninushi_id >= 20,000,000) THEN (hashed SubPlan 6) ELSE true END
47. 39.675 310.685 ↑ 1.0 706,148 1

Append (cost=0.00..46,265.72 rows=711,773 width=416) (actual time=0.011..310.685 rows=706,148 loops=1)

48. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on proposal_detail pd (cost=0.00..0.00 rows=1 width=92) (actual time=0.005..0.005 rows=0 loops=1)

49. 0.052 0.052 ↑ 1.0 120 1

Seq Scan on proposal_detail pd_1 (cost=0.00..4.20 rows=120 width=114) (actual time=0.005..0.052 rows=120 loops=1)

50. 270.953 270.953 ↑ 1.0 706,028 1

Seq Scan on proposal_detail pd_2 (cost=0.00..46,261.52 rows=711,652 width=416) (actual time=0.047..270.953 rows=706,028 loops=1)

51. 1.405 20.229 ↑ 1.6 4,760 1

Hash (cost=2,891.20..2,891.20 rows=7,732 width=66) (actual time=20.229..20.229 rows=4,760 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 561kB
52. 4.796 18.824 ↑ 1.6 4,760 1

Hash Join (cost=539.13..2,891.20 rows=7,732 width=66) (actual time=1.856..18.824 rows=4,760 loops=1)

  • Hash Cond: (ppl.proposal_id = t0.id)
53. 2.353 12.252 ↑ 1.0 35,104 1

Append (cost=0.00..2,142.82 rows=35,183 width=39) (actual time=0.004..12.252 rows=35,104 loops=1)

54. 0.001 0.001 ↓ 0.0 0 1

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

55. 0.005 0.005 ↑ 1.0 7 1

Seq Scan on proposal_panel ppl_1 (cost=0.00..1.07 rows=7 width=25) (actual time=0.003..0.005 rows=7 loops=1)

56. 9.893 9.893 ↑ 1.0 35,097 1

Seq Scan on proposal_panel ppl_2 (cost=0.00..2,141.75 rows=35,175 width=39) (actual time=0.005..9.893 rows=35,097 loops=1)

57. 0.310 1.776 ↑ 1.0 1,378 1

Hash (cost=521.70..521.70 rows=1,394 width=27) (actual time=1.776..1.776 rows=1,378 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 100kB
58. 0.090 1.466 ↑ 1.0 1,378 1

Append (cost=0.00..521.70 rows=1,394 width=27) (actual time=0.014..1.466 rows=1,378 loops=1)

59. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((auction_date >= '2019-04-01'::date) AND (active = 1) AND (publish = 1))
60. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on proposal t0_1 (cost=0.00..1.12 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((auction_date >= '2019-04-01'::date) AND (active = 1) AND (publish = 1))
  • Rows Removed by Filter: 7
61. 1.371 1.371 ↑ 1.0 1,378 1

Index Scan using proposal_auction_date_idx on proposal t0_2 (cost=0.28..520.58 rows=1,392 width=27) (actual time=0.009..1.371 rows=1,378 loops=1)

  • Index Cond: (auction_date >= '2019-04-01'::date)
  • Filter: ((active = 1) AND (publish = 1))
  • Rows Removed by Filter: 531
62.          

SubPlan (for Hash Join)

63. 0.000 0.000 ↓ 0.0 0

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

64. 6.917 360.516 ↑ 1.5 3,470 1

Hash (cost=88,297.40..88,297.40 rows=5,036 width=68) (actual time=360.516..360.516 rows=3,470 loops=1)

  • Buckets: 8,192 (originally 8192) Batches: 4 (originally 1) Memory Usage: 4,035kB
65. 0.220 353.599 ↑ 1.5 3,470 1

Nested Loop (cost=84,936.56..88,297.40 rows=5,036 width=68) (actual time=349.693..353.599 rows=3,470 loops=1)

  • Join Filter: (paths.parent_id = ANY ((array_agg(ppbl.market_id))))
  • Rows Removed by Join Filter: 240
66. 1.390 345.959 ↓ 18.0 1,855 1

GroupAggregate (cost=84,936.56..84,938.87 rows=103 width=40) (actual time=344.386..345.959 rows=1,855 loops=1)

  • Group Key: ppbl.proposal_id, ppbl.customer_id
67. 1.278 344.569 ↓ 33.7 3,470 1

Sort (cost=84,936.56..84,936.81 rows=103 width=12) (actual time=344.375..344.569 rows=3,470 loops=1)

  • Sort Key: ppbl.proposal_id
  • Sort Method: quicksort Memory: 259kB
68. 1.033 343.291 ↓ 33.7 3,470 1

Nested Loop Semi Join (cost=63.67..84,933.11 rows=103 width=12) (actual time=0.174..343.291 rows=3,470 loops=1)

69. 1.910 342.258 ↓ 33.7 3,470 1

Hash Join (cost=63.67..78,193.25 rows=103 width=12) (actual time=0.165..342.258 rows=3,470 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: 3,260
70. 0.316 340.317 ↓ 1.7 3,470 1

Append (cost=0.00..77,341.52 rows=1,998 width=16) (actual time=0.127..340.317 rows=3,470 loops=1)

71. 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 = 1,677)
72. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: (customer_id = 1,677)
73. 339.998 339.998 ↓ 1.7 3,470 1

Seq Scan on proposal_publish ppbl_2 (cost=0.00..77,318.15 rows=1,992 width=16) (actual time=0.124..339.998 rows=3,470 loops=1)

  • Filter: (customer_id = 1,677)
  • Rows Removed by Filter: 3,536,869
74. 0.016 0.031 ↑ 16.3 120 1

Hash (cost=39.18..39.18 rows=1,959 width=8) (actual time=0.031..0.031 rows=120 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 21kB
75. 0.015 0.015 ↑ 16.3 120 1

CTE Scan on ninushis_customers nnscs (cost=0.00..39.18 rows=1,959 width=8) (actual time=0.001..0.015 rows=120 loops=1)

76. 0.000 0.000 ↑ 1,892.0 1 3,470

Materialize (cost=0.00..6,737.30 rows=1,892 width=4) (actual time=0.000..0.000 rows=1 loops=3,470)

77. 0.001 0.006 ↑ 1,892.0 1 1

Subquery Scan on ANY_subquery (cost=0.00..6,727.84 rows=1,892 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Filter: ("ANY_subquery".unnest = 1,677)
  • Rows Removed by Filter: 2
78. 0.004 0.005 ↑ 126,166.7 3 1

ProjectSet (cost=0.00..1,996.59 rows=378,500 width=4) (actual time=0.005..0.005 rows=3 loops=1)

79. 0.001 0.001 ↑ 3,785.0 1 1

CTE Scan on customer_parents (cost=0.00..75.70 rows=3,785 width=32) (actual time=0.001..0.001 rows=1 loops=1)

80. 7.420 7.420 ↑ 500.0 2 1,855

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

81. 6.148 13.585 ↓ 1.0 34,715 1

Hash (cost=921.20..921.20 rows=34,710 width=4) (actual time=13.585..13.585 rows=34,715 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,733kB
82. 2.118 7.437 ↓ 1.0 34,715 1

Append (cost=0.00..921.20 rows=34,710 width=4) (actual time=0.019..7.437 rows=34,715 loops=1)

83. 0.003 0.003 ↓ 0.0 0 1

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

84. 5.126 5.126 ↓ 1.0 33,335 1

Index Only Scan using catalog_pkey on catalog ctg_1 (cost=0.29..876.25 rows=33,331 width=4) (actual time=0.016..5.126 rows=33,335 loops=1)

  • Heap Fetches: 2,087
85. 0.190 0.190 ↓ 1.0 1,380 1

Index Only Scan using catalog_brand_pkey on catalog_brand ctg_2 (cost=0.28..44.95 rows=1,378 width=4) (actual time=0.015..0.190 rows=1,380 loops=1)

  • Heap Fetches: 27
86. 139.372 139.372 ↑ 1.0 1 139,372

Result (cost=0.00..0.53 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=139,372)

87. 0.054 0.927 ↑ 1.0 320 1

Hash (cost=173.31..173.31 rows=320 width=36) (actual time=0.927..0.927 rows=320 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
88. 0.035 0.873 ↑ 1.0 320 1

Subquery Scan on nnk (cost=166.91..173.31 rows=320 width=36) (actual time=0.786..0.873 rows=320 loops=1)

89. 0.157 0.838 ↑ 1.0 320 1

HashAggregate (cost=166.91..170.11 rows=320 width=36) (actual time=0.785..0.838 rows=320 loops=1)

  • Group Key: ninushi.id, ninushi.nick
90. 0.020 0.681 ↑ 1.0 320 1

Append (cost=0.00..165.31 rows=320 width=36) (actual time=0.008..0.681 rows=320 loops=1)

91. 0.031 0.031 ↑ 1.0 96 1

Seq Scan on ninushi (cost=0.00..8.96 rows=96 width=16) (actual time=0.008..0.031 rows=96 loops=1)

92. 0.630 0.630 ↑ 1.0 224 1

Seq Scan on customer (cost=0.00..153.15 rows=224 width=18) (actual time=0.003..0.630 rows=224 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1,949
93. 0.010 723.931 ↑ 5.7 35 1

Hash (cost=48.08..48.08 rows=200 width=4) (actual time=723.931..723.931 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
94. 0.033 723.921 ↑ 5.7 35 1

HashAggregate (cost=44.08..46.08 rows=200 width=4) (actual time=723.915..723.921 rows=35 loops=1)

  • Group Key: ninushis_customers.ninushi_id
95. 723.888 723.888 ↑ 16.3 120 1

CTE Scan on ninushis_customers (cost=0.00..39.18 rows=1,959 width=4) (actual time=723.830..723.888 rows=120 loops=1)

96.          

SubPlan (for GroupAggregate)

97. 418.116 418.116 ↑ 1.0 1 139,372

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=139,372)

Planning time : 6.661 ms
Execution time : 16,162.066 ms