explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6faw

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 29,650.673 ↑ 1.0 1 1

Aggregate (cost=14,967,931.57..14,967,931.58 rows=1 width=8) (actual time=29,650.673..29,650.673 rows=1 loops=1)

2. 0.095 29,650.670 ↑ 3,160.0 5 1

HashAggregate (cost=14,967,576.07..14,967,734.07 rows=15,800 width=110) (actual time=29,650.589..29,650.670 rows=5 loops=1)

  • Group Key: ninushi.nick, t0.id
3.          

CTE paths

4. 5.807 5.807 ↑ 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.803..5.807 rows=2 loops=1)

5.          

CTE customer_paths

6. 0.456 730.339 ↑ 391.7 2,093 1

Recursive Union (cost=0.00..1,403,776.46 rows=819,898 width=61) (actual time=0.009..730.339 rows=2,093 loops=1)

7. 0.748 0.748 ↑ 1.0 226 1

Seq Scan on customer customer_1 (cost=0.00..155.57 rows=228 width=61) (actual time=0.008..0.748 rows=226 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1957
8. 529.988 729.135 ↑ 131.8 622 3

Nested Loop (cost=0.00..138,722.29 rows=81,967 width=61) (actual time=33.477..243.045 rows=622 loops=3)

  • Join Filter: ((customer_paths.level < customer_2.level) AND (customer_paths.id = ANY (customer_2.parent_ids)))
  • Rows Removed by Join Filter: 1522384
9. 0.312 0.312 ↑ 3.3 698 3

WorkTable Scan on customer_paths (cost=0.00..45.60 rows=2,280 width=38) (actual time=0.000..0.104 rows=698 loops=3)

10. 198.295 198.835 ↑ 1.0 2,183 2,093

Materialize (cost=0.00..161.09 rows=2,206 width=29) (actual time=0.000..0.095 rows=2,183 loops=2,093)

11. 0.540 0.540 ↑ 1.0 2,183 1

Seq Scan on customer customer_2 (cost=0.00..150.06 rows=2,206 width=29) (actual time=0.002..0.540 rows=2,183 loops=1)

12.          

CTE customer_parents

13. 0.020 731.038 ↑ 3,905.0 1 1

GroupAggregate (cost=71,865.46..76,013.27 rows=3,905 width=38) (actual time=731.038..731.038 rows=1 loops=1)

  • Group Key: customer_paths_1.id, customer_paths_1.level
14. 0.007 731.018 ↑ 102,475.0 4 1

Sort (cost=71,865.46..72,890.21 rows=409,900 width=10) (actual time=731.018..731.018 rows=4 loops=1)

  • Sort Key: customer_paths_1.level
  • Sort Method: quicksort Memory: 25kB
15. 0.007 731.011 ↑ 102,475.0 4 1

Nested Loop (cost=0.00..26,645.71 rows=409,900 width=10) (actual time=81.333..731.011 rows=4 loops=1)

16. 730.988 730.988 ↑ 2,049.5 2 1

CTE Scan on customer_paths customer_paths_1 (cost=0.00..18,447.70 rows=4,099 width=38) (actual time=81.319..730.988 rows=2 loops=1)

  • Filter: (id = 482)
  • Rows Removed by Filter: 2091
17. 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)

18.          

CTE ninushis_customers

19. 0.379 756.110 ↑ 17.2 110 1

HashAggregate (cost=19,687.53..19,706.41 rows=1,888 width=8) (actual time=756.084..756.110 rows=110 loops=1)

  • Group Key: ninushi_customer.ninushi_id, ninushi_customer.customer_id
20. 0.102 755.731 ↓ 1.0 1,893 1

Append (cost=6,945.64..19,678.09 rows=1,888 width=8) (actual time=731.099..755.731 rows=1,893 loops=1)

21. 0.169 731.467 ↑ 15.2 61 1

Hash Join (cost=6,945.64..7,031.47 rows=930 width=8) (actual time=731.099..731.467 rows=61 loops=1)

  • Hash Cond: (ninushi_customer.customer_id = (unnest(customer_parents_1.parents_and_self)))
22. 0.246 0.246 ↑ 1.0 1,803 1

Seq Scan on ninushi_customer (cost=0.00..70.60 rows=1,860 width=8) (actual time=0.015..0.246 rows=1,803 loops=1)

23. 0.002 731.052 ↑ 66.7 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.005 731.050 ↑ 66.7 3 1

HashAggregate (cost=6,941.14..6,943.14 rows=200 width=4) (actual time=731.048..731.050 rows=3 loops=1)

  • Group Key: unnest(customer_parents_1.parents_and_self)
25. 0.005 731.045 ↑ 130,166.7 3 1

ProjectSet (cost=0.00..2,059.89 rows=390,500 width=4) (actual time=731.044..731.045 rows=3 loops=1)

26. 731.040 731.040 ↑ 3,905.0 1 1

CTE Scan on customer_parents customer_parents_1 (cost=0.00..78.10 rows=3,905 width=32) (actual time=731.040..731.040 rows=1 loops=1)

27. 17.355 24.162 ↓ 1.9 1,832 1

Nested Loop (cost=7,104.50..12,627.74 rows=958 width=8) (actual time=5.249..24.162 rows=1,832 loops=1)

  • Join Filter: (market.id = ANY (buyer.parent_ids))
  • Rows Removed by Join Filter: 131331
28. 0.258 0.258 ↑ 1.0 2,183 1

Seq Scan on customer buyer (cost=0.00..150.06 rows=2,206 width=27) (actual time=0.004..0.258 rows=2,183 loops=1)

29. 5.677 6.549 ↑ 1.6 61 2,183

Materialize (cost=7,104.50..7,183.52 rows=96 width=8) (actual time=0.000..0.003 rows=61 loops=2,183)

30. 0.171 0.872 ↑ 1.6 61 1

Hash Join (cost=7,104.50..7,183.04 rows=96 width=8) (actual time=0.599..0.872 rows=61 loops=1)

  • Hash Cond: (nnc.customer_id = market.id)
31. 0.122 0.122 ↑ 1.0 1,803 1

Seq Scan on ninushi_customer nnc (cost=0.00..70.60 rows=1,860 width=4) (actual time=0.001..0.122 rows=1,803 loops=1)

32. 0.001 0.579 ↑ 57.0 2 1

Hash (cost=7,103.08..7,103.08 rows=114 width=12) (actual time=0.579..0.579 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.020 0.578 ↑ 57.0 2 1

Hash Join (cost=6,945.64..7,103.08 rows=114 width=12) (actual time=0.099..0.578 rows=2 loops=1)

  • Hash Cond: (market.id = (unnest(customer_parents_2.parents_and_self)))
34. 0.551 0.551 ↑ 1.0 226 1

Seq Scan on customer market (cost=0.00..155.57 rows=228 width=8) (actual time=0.002..0.551 rows=226 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1957
35. 0.001 0.007 ↑ 66.7 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.003 0.006 ↑ 66.7 3 1

HashAggregate (cost=6,941.14..6,943.14 rows=200 width=4) (actual time=0.005..0.006 rows=3 loops=1)

  • Group Key: unnest(customer_parents_2.parents_and_self)
37. 0.002 0.003 ↑ 130,166.7 3 1

ProjectSet (cost=0.00..2,059.89 rows=390,500 width=4) (actual time=0.002..0.003 rows=3 loops=1)

38. 0.001 0.001 ↑ 3,905.0 1 1

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

39. 0.011 29,650.575 ↑ 88,629,506.4 7 1

Hash Join (cost=86,864.72..10,366,024.46 rows=620,406,545 width=40) (actual time=20,370.137..29,650.575 rows=7 loops=1)

  • Hash Cond: (ninushi.id = ninushis_customers.ninushi_id)
40. 4.332 28,894.386 ↑ 88,629,506.4 7 1

Nested Loop (cost=86,815.74..2,560,873.39 rows=620,406,545 width=44) (actual time=19,613.953..28,894.386 rows=7 loops=1)

41. 3.286 381.431 ↑ 5.2 4,433 1

Nested Loop (cost=86,322.66..98,059.04 rows=22,880 width=68) (actual time=366.106..381.431 rows=4,433 loops=1)

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

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

43. 2.492 372.324 ↓ 7.6 3,536 2

Materialize (cost=86,322.66..86,340.21 rows=468 width=36) (actual time=180.149..186.162 rows=3,536 loops=2)

44. 1.185 369.832 ↓ 7.6 3,536 1

Subquery Scan on ppb (cost=86,322.66..86,337.87 rows=468 width=36) (actual time=360.296..369.832 rows=3,536 loops=1)

45. 7.090 368.647 ↓ 7.6 3,536 1

GroupAggregate (cost=86,322.66..86,333.19 rows=468 width=40) (actual time=360.295..368.647 rows=3,536 loops=1)

  • Group Key: ppbl.proposal_id, ppbl.customer_id
46. 2.646 361.557 ↓ 9.5 4,433 1

Sort (cost=86,322.66..86,323.83 rows=468 width=12) (actual time=360.287..361.557 rows=4,433 loops=1)

  • Sort Key: ppbl.proposal_id
  • Sort Method: quicksort Memory: 400kB
47. 1.207 358.911 ↓ 9.5 4,433 1

Nested Loop Semi Join (cost=61.36..86,301.90 rows=468 width=12) (actual time=0.043..358.911 rows=4,433 loops=1)

48. 2.155 357.704 ↓ 9.5 4,433 1

Hash Join (cost=61.36..79,339.32 rows=468 width=12) (actual time=0.038..357.704 rows=4,433 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: 4012
49. 0.392 355.529 ↑ 1.1 4,433 1

Append (cost=0.00..77,389.14 rows=4,966 width=16) (actual time=0.009..355.529 rows=4,433 loops=1)

50. 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)
51. 0.007 0.007 ↑ 2.5 2 1

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

  • Filter: (customer_id = 482)
52. 355.128 355.128 ↑ 1.1 4,431 1

Seq Scan on proposal_publish ppbl_2 (cost=0.00..77,365.76 rows=4,960 width=16) (actual time=1.129..355.128 rows=4,431 loops=1)

  • Filter: (customer_id = 482)
  • Rows Removed by Filter: 3537955
53. 0.011 0.020 ↑ 17.2 110 1

Hash (cost=37.76..37.76 rows=1,888 width=8) (actual time=0.020..0.020 rows=110 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 21kB
54. 0.009 0.009 ↑ 17.2 110 1

CTE Scan on ninushis_customers nnscs (cost=0.00..37.76 rows=1,888 width=8) (actual time=0.001..0.009 rows=110 loops=1)

55. 0.000 0.000 ↑ 1,952.0 1 4,433

Materialize (cost=0.00..6,950.90 rows=1,952 width=4) (actual time=0.000..0.000 rows=1 loops=4,433)

56. 0.001 0.004 ↑ 1,952.0 1 1

Subquery Scan on ANY_subquery (cost=0.00..6,941.14 rows=1,952 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: ("ANY_subquery".unnest = 482)
  • Rows Removed by Filter: 2
57. 0.002 0.003 ↑ 130,166.7 3 1

ProjectSet (cost=0.00..2,059.89 rows=390,500 width=4) (actual time=0.002..0.003 rows=3 loops=1)

58. 0.001 0.001 ↑ 3,905.0 1 1

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

59. 0.000 28,508.623 ↓ 0.0 0 4,433

Hash Join (cost=493.08..590.41 rows=1,029 width=448) (actual time=6.429..6.431 rows=0 loops=4,433)

  • Hash Cond: (pd.catalog_id = ctg.id)
60. 35.957 28,508.623 ↑ 2,745.0 1 4,433

Nested Loop (cost=281.85..358.60 rows=2,745 width=452) (actual time=6.423..6.431 rows=1 loops=4,433)

61. 8.866 62.062 ↑ 1.3 6 4,433

Append (cost=0.00..13.59 rows=8 width=8) (actual time=0.005..0.014 rows=6 loops=4,433)

62. 0.000 0.000 ↓ 0.0 0 4,433

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

  • Filter: (ppb.proposal_id = proposal_id)
63. 4.433 4.433 ↓ 0.0 0 4,433

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

  • Index Cond: (proposal_id = ppb.proposal_id)
64. 48.763 48.763 ↑ 1.0 6 4,433

Index Scan using proposal_panel_proposal_id_idx on proposal_panel ppl_2 (cost=0.29..13.41 rows=6 width=8) (actual time=0.003..0.011 rows=6 loops=4,433)

  • Index Cond: (proposal_id = ppb.proposal_id)
65. 52.660 28,410.604 ↓ 0.0 0 28,814

Hash Join (cost=281.85..289.65 rows=10 width=452) (actual time=0.986..0.986 rows=0 loops=28,814)

  • Hash Cond: (ninushi.id = t0.ninushi_id)
66. 3.971 4.968 ↑ 1.0 323 92

HashAggregate (cost=169.39..172.63 rows=324 width=36) (actual time=0.013..0.054 rows=323 loops=92)

  • Group Key: ninushi.id, ninushi.nick
67. 0.025 0.997 ↑ 1.0 323 1

Append (cost=0.00..167.77 rows=324 width=36) (actual time=0.010..0.997 rows=323 loops=1)

68. 0.055 0.055 ↓ 1.0 97 1

Seq Scan on ninushi (cost=0.00..8.96 rows=96 width=16) (actual time=0.010..0.055 rows=97 loops=1)

69. 0.917 0.917 ↑ 1.0 226 1

Seq Scan on customer (cost=0.00..155.57 rows=228 width=18) (actual time=0.008..0.917 rows=226 loops=1)

  • Filter: (level = 1)
  • Rows Removed by Filter: 1957
70. 28.814 28,352.976 ↓ 0.0 0 28,814

Hash (cost=112.38..112.38 rows=6 width=416) (actual time=0.984..0.984 rows=0 loops=28,814)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
71. 879.306 28,324.162 ↓ 0.0 0 28,814

Nested Loop (cost=42.48..112.38 rows=6 width=416) (actual time=0.978..0.983 rows=0 loops=28,814)

  • Join Filter: CASE WHEN (t0.ninushi_id >= 20000000) THEN (hashed SubPlan 5) ELSE true END
72. 24,463.086 25,385.134 ↑ 1.2 24 28,814

Nested Loop (cost=0.00..53.41 rows=28 width=408) (actual time=0.058..0.881 rows=24 loops=28,814)

  • 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: 1
73. 115.256 922.048 ↑ 3.5 24 28,814

Append (cost=0.00..8.05 rows=84 width=419) (actual time=0.006..0.032 rows=24 loops=28,814)

74. 0.000 0.000 ↓ 0.0 0 28,814

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

  • Filter: (ppl.id = proposal_panel_id)
75. 28.814 28.814 ↓ 0.0 0 28,814

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_1 (cost=0.27..0.98 rows=40 width=127) (actual time=0.001..0.001 rows=0 loops=28,814)

  • Index Cond: (proposal_panel_id = ppl.id)
76. 777.978 777.978 ↑ 1.8 24 28,814

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_2 (cost=0.42..7.07 rows=43 width=419) (actual time=0.004..0.027 rows=24 loops=28,814)

  • Index Cond: (proposal_panel_id = ppl.id)
77. 0.000 0.000 ↑ 1.0 1 701,262

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=701,262)

78. 0.000 2,059.722 ↓ 0.0 0 686,574

Append (cost=0.00..0.53 rows=3 width=8) (actual time=0.003..0.003 rows=0 loops=686,574)

79. 0.000 0.000 ↓ 0.0 0 686,574

Seq Scan on proposal t0 (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=686,574)

  • Filter: ((auction_date >= '2019-08-23'::date) AND (active = 1) AND (publish = 1) AND (ppl.proposal_id = id))
80. 686.574 686.574 ↓ 0.0 0 686,574

Index Scan using proposal_pkey on proposal t0_1 (cost=0.13..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=686,574)

  • Index Cond: (id = ppl.proposal_id)
  • Filter: ((auction_date >= '2019-08-23'::date) AND (active = 1) AND (publish = 1))
  • Rows Removed by Filter: 0
81. 1,373.148 1,373.148 ↓ 0.0 0 686,574

Index Scan using proposal_pkey on proposal t0_2 (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=686,574)

  • Index Cond: (id = ppl.proposal_id)
  • Filter: ((auction_date >= '2019-08-23'::date) AND (active = 1) AND (publish = 1))
  • Rows Removed by Filter: 1
82.          

SubPlan (forNested Loop)

83. 0.000 0.000 ↓ 0.0 0

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

84. 0.006 0.168 ↑ 2.1 35 1

Hash (cost=210.29..210.29 rows=75 width=4) (actual time=0.168..0.168 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
85. 0.003 0.162 ↑ 2.1 35 1

Append (cost=0.00..210.29 rows=75 width=4) (actual time=0.023..0.162 rows=35 loops=1)

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

  • Filter: (id = ANY ('{44186,44181,34064,28812,38148,372,403,39967,34016,127,16497,371,2704,22067,36306,21430,1011,40064,502,33066,26126,28476,376,38277,22068,16498,32435,35808,2705,28180,37064,21204,37150,30671,41853,128,23665}'::integer[]))
87. 0.112 0.112 ↑ 1.1 34 1

Index Only Scan using catalog_pkey on catalog ctg_1 (cost=0.29..151.38 rows=37 width=4) (actual time=0.020..0.112 rows=34 loops=1)

  • Index Cond: (id = ANY ('{44186,44181,34064,28812,38148,372,403,39967,34016,127,16497,371,2704,22067,36306,21430,1011,40064,502,33066,26126,28476,376,38277,22068,16498,32435,35808,2705,28180,37064,21204,37150,30671,41853,128,23665}'::integer[]))
  • Heap Fetches: 1
88. 0.044 0.044 ↑ 37.0 1 1

Index Only Scan using catalog_brand_pkey on catalog_brand ctg_2 (cost=0.28..58.91 rows=37 width=4) (actual time=0.022..0.044 rows=1 loops=1)

  • Index Cond: (id = ANY ('{44186,44181,34064,28812,38148,372,403,39967,34016,127,16497,371,2704,22067,36306,21430,1011,40064,502,33066,26126,28476,376,38277,22068,16498,32435,35808,2705,28180,37064,21204,37150,30671,41853,128,23665}'::integer[]))
  • Heap Fetches: 0
89. 0.009 756.178 ↑ 4.3 47 1

Hash (cost=46.48..46.48 rows=200 width=4) (actual time=756.178..756.178 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
90. 0.038 756.169 ↑ 4.3 47 1

HashAggregate (cost=42.48..44.48 rows=200 width=4) (actual time=756.163..756.169 rows=47 loops=1)

  • Group Key: ninushis_customers.ninushi_id
91. 756.131 756.131 ↑ 17.2 110 1

CTE Scan on ninushis_customers (cost=0.00..37.76 rows=1,888 width=4) (actual time=756.085..756.131 rows=110 loops=1)

Planning time : 6.098 ms
Execution time : 29,651.518 ms