explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r6K4 : 1st longest query on user admin

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 4,705.730 ↑ 1,462,718,880.0 1 1

Nested Loop Left Join (cost=1,946,710.32..82,701,637.68 rows=1,462,718,880 width=724) (actual time=4,705.716..4,705.730 rows=1 loops=1)

  • Join Filter: (beyond_users.id = u.id)
2. 0.035 0.035 ↑ 1.0 1 1

Index Scan using beyond_user_pkey on beyond_users (cost=0.41..8.44 rows=1 width=680) (actual time=0.034..0.035 rows=1 loops=1)

  • Index Cond: (id = 17355)
3. 0.026 4,705.688 ↑ 1,462,718,880.0 1 1

Hash Left Join (cost=1,946,709.90..49,790,454.45 rows=1,462,718,880 width=52) (actual time=4,705.676..4,705.688 rows=1 loops=1)

  • Hash Cond: (u.id = mas.user_id)
4.          

CTE listings_summary

5. 10.181 854.360 ↓ 3.2 20,344 1

Finalize GroupAggregate (cost=628,411.87..628,632.37 rows=6,300 width=28) (actual time=839.435..854.360 rows=20,344 loops=1)

  • Group Key: beyond_listings.user_id
6. 15.922 844.179 ↓ 3.0 38,012 1

Sort (cost=628,411.87..628,443.37 rows=12,600 width=28) (actual time=839.428..844.179 rows=38,012 loops=1)

  • Sort Key: beyond_listings.user_id
  • Sort Method: quicksort Memory: 3915kB
7. 14.314 828.257 ↓ 3.0 38,012 1

Gather (cost=626,230.74..627,553.74 rows=12,600 width=28) (actual time=814.189..828.257 rows=38,012 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 34.831 813.943 ↓ 2.0 12,671 3

Partial HashAggregate (cost=625,230.74..625,293.74 rows=6,300 width=28) (actual time=809.993..813.943 rows=12,671 loops=3)

  • Group Key: beyond_listings.user_id
9. 779.112 779.112 ↑ 1.3 100,298 3

Parallel Seq Scan on beyond_listings (cost=0.00..623,974.15 rows=125,659 width=10) (actual time=0.082..779.112 rows=100,298 loops=3)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 37987
10.          

CTE managed_accounts_summary

11. 9.170 3,065.402 ↑ 1.4 21,247 1

GroupAggregate (cost=1,243,405.27..1,244,492.75 rows=30,138 width=28) (actual time=3,054.043..3,065.402 rows=21,247 loops=1)

  • Group Key: beyond_managed_accounts_summary.user_id
12. 9.290 3,056.232 ↑ 2.2 28,304 1

Sort (cost=1,243,405.27..1,243,562.49 rows=62,888 width=16) (actual time=3,054.034..3,056.232 rows=28,304 loops=1)

  • Sort Key: beyond_managed_accounts_summary.user_id
  • Sort Method: quicksort Memory: 2095kB
13. 2.959 3,046.942 ↑ 2.2 28,304 1

Subquery Scan on beyond_managed_accounts_summary (cost=1,237,242.22..1,238,392.94 rows=62,888 width=16) (actual time=3,008.119..3,046.942 rows=28,304 loops=1)

14. 11.580 3,043.983 ↑ 2.2 28,304 1

Hash Right Join (cost=1,237,242.22..1,237,764.06 rows=62,888 width=32) (actual time=3,008.118..3,043.983 rows=28,304 loops=1)

  • Hash Cond: (cls.managed_account_id = ma.id)
15.          

CTE channel_listings_summary

16. 12.350 2,942.869 ↓ 3.0 27,165 1

Finalize GroupAggregate (cost=1,171,378.81..1,171,745.49 rows=9,167 width=36) (actual time=2,925.680..2,942.869 rows=27,165 loops=1)

  • Group Key: beyond_channel_listings.managed_account_id
17. 12.467 2,930.519 ↓ 1.9 34,984 1

Sort (cost=1,171,378.81..1,171,424.65 rows=18,334 width=36) (actual time=2,925.673..2,930.519 rows=34,984 loops=1)

  • Sort Key: beyond_channel_listings.managed_account_id
  • Sort Method: quicksort Memory: 3679kB
18. 83.861 2,918.052 ↓ 1.9 34,984 1

Gather (cost=1,168,155.49..1,170,080.56 rows=18,334 width=36) (actual time=2,904.413..2,918.052 rows=34,984 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 31.443 2,834.191 ↓ 1.3 11,661 3

Partial HashAggregate (cost=1,167,155.49..1,167,247.16 rows=9,167 width=36) (actual time=2,832.059..2,834.191 rows=11,661 loops=3)

  • Group Key: beyond_channel_listings.managed_account_id
20. 68.756 2,802.748 ↑ 2.5 101,448 3

Hash Join (cost=633,204.41..1,164,004.14 rows=252,108 width=11) (actual time=2,264.740..2,802.748 rows=101,448 loops=3)

  • Hash Cond: (beyond_channel_listings.master_listing_id = beyond_listings_1.id)
21. 480.748 480.748 ↑ 2.5 101,448 3

Parallel Seq Scan on beyond_channel_listings (cost=0.00..526,053.95 rows=252,108 width=13) (actual time=0.016..480.748 rows=101,448 loops=3)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 38007
22. 73.872 2,253.244 ↑ 1.0 414,856 3

Hash (cost=626,394.96..626,394.96 rows=414,996 width=6) (actual time=2,253.244..2,253.244 rows=414,856 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3049kB
23. 2,179.372 2,179.372 ↑ 1.0 414,856 3

Seq Scan on beyond_listings beyond_listings_1 (cost=0.00..626,394.96 rows=414,996 width=6) (actual time=0.100..2,179.372 rows=414,856 loops=3)

24. 2,950.359 2,950.359 ↓ 3.0 27,165 1

CTE Scan on channel_listings_summary cls (cost=0.00..183.34 rows=9,167 width=20) (actual time=2,925.684..2,950.359 rows=27,165 loops=1)

25. 5.204 82.044 ↑ 2.2 28,304 1

Hash (cost=64,710.63..64,710.63 rows=62,888 width=8) (actual time=82.043..82.044 rows=28,304 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1618kB
26. 76.840 76.840 ↑ 2.2 28,304 1

Seq Scan on beyond_managed_accounts ma (cost=0.00..64,710.63 rows=62,888 width=8) (actual time=0.017..76.840 rows=28,304 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 8630
27.          

CTE statements_summary

28. 136.469 265.558 ↓ 1.3 24,011 1

HashAggregate (cost=22,096.53..22,281.84 rows=18,531 width=16) (actual time=262.033..265.558 rows=24,011 loops=1)

  • Group Key: beyond_statements.user_id
29. 129.089 129.089 ↑ 1.0 643,031 1

Seq Scan on beyond_statements (cost=0.00..14,017.90 rows=646,290 width=21) (actual time=0.023..129.089 rows=643,031 loops=1)

30.          

CTE cards_summary

31. 4.136 5.560 ↑ 1.0 6,907 1

HashAggregate (cost=309.87..379.59 rows=6,972 width=12) (actual time=4.396..5.560 rows=6,907 loops=1)

  • Group Key: beyond_cards.user_id
32. 1.424 1.424 ↓ 1.0 10,246 1

Seq Scan on beyond_cards (cost=0.00..258.91 rows=10,191 width=8) (actual time=0.010..1.424 rows=10,246 loops=1)

33.          

CTE payments_summary

34. 27.530 491.485 ↓ 1.2 23,140 1

Finalize HashAggregate (cost=49,032.94..49,264.58 rows=18,531 width=36) (actual time=487.296..491.485 rows=23,140 loops=1)

  • Group Key: beyond_statements_1.user_id
35. 45.587 463.955 ↓ 1.8 65,920 1

Gather (cost=44,817.14..48,754.98 rows=37,062 width=36) (actual time=424.148..463.955 rows=65,920 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
36. 49.566 418.368 ↓ 1.2 21,973 3

Partial HashAggregate (cost=43,817.14..44,048.78 rows=18,531 width=36) (actual time=412.505..418.368 rows=21,973 loops=3)

  • Group Key: beyond_statements_1.user_id
37. 106.991 368.802 ↑ 1.2 163,319 3

Hash Left Join (cost=24,621.53..42,107.00 rows=195,444 width=12) (actual time=214.291..368.802 rows=163,319 loops=3)

  • Hash Cond: (beyond_payments.statement_id = beyond_statements_1.id)
38. 48.388 48.388 ↑ 1.2 163,319 3

Parallel Seq Scan on beyond_payments (cost=0.00..12,537.44 rows=195,444 width=12) (actual time=0.015..48.388 rows=163,319 loops=3)

39. 97.730 213.423 ↑ 1.0 643,031 3

Hash (cost=14,017.90..14,017.90 rows=646,290 width=8) (actual time=213.423..213.423 rows=643,031 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2601kB
40. 115.693 115.693 ↑ 1.0 643,031 3

Seq Scan on beyond_statements beyond_statements_1 (cost=0.00..14,017.90 rows=646,290 width=8) (actual time=0.018..115.693 rows=643,031 loops=3)

41. 0.021 1,634.380 ↑ 9,686,880.0 1 1

Hash Left Join (cost=978.79..100,513.53 rows=9,686,880 width=80) (actual time=1,634.369..1,634.380 rows=1 loops=1)

  • Hash Cond: (u.id = ps.user_id)
42. 0.022 1,137.923 ↑ 104,160.0 1 1

Hash Left Join (cost=560.68..1,794.42 rows=104,160 width=48) (actual time=1,137.913..1,137.923 rows=1 loops=1)

  • Hash Cond: (u.id = ss.user_id)
43. 0.012 866.881 ↑ 1,120.0 1 1

Hash Left Join (cost=142.56..319.31 rows=1,120 width=36) (actual time=866.872..866.881 rows=1 loops=1)

  • Hash Cond: (u.id = mls.user_id)
44. 0.002 7.294 ↑ 35.0 1 1

Nested Loop Left Join (cost=0.41..165.74 rows=35 width=12) (actual time=7.286..7.294 rows=1 loops=1)

  • Join Filter: (u.id = cs.user_id)
45. 0.013 0.013 ↑ 1.0 1 1

Index Only Scan using beyond_user_pkey on beyond_users u (cost=0.41..8.43 rows=1 width=4) (actual time=0.005..0.013 rows=1 loops=1)

  • Index Cond: (id = 17355)
  • Heap Fetches: 0
46. 7.279 7.279 ↓ 0.0 0 1

CTE Scan on cards_summary cs (cost=0.00..156.87 rows=35 width=12) (actual time=7.279..7.279 rows=0 loops=1)

  • Filter: (user_id = 17355)
  • Rows Removed by Filter: 6907
47. 0.006 859.575 ↑ 32.0 1 1

Hash (cost=141.75..141.75 rows=32 width=28) (actual time=859.575..859.575 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 859.569 859.569 ↑ 32.0 1 1

CTE Scan on listings_summary mls (cost=0.00..141.75 rows=32 width=28) (actual time=848.528..859.569 rows=1 loops=1)

  • Filter: (user_id = 17355)
  • Rows Removed by Filter: 20343
49. 0.004 271.020 ↑ 93.0 1 1

Hash (cost=416.95..416.95 rows=93 width=16) (actual time=271.020..271.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 271.016 271.016 ↑ 93.0 1 1

CTE Scan on statements_summary ss (cost=0.00..416.95 rows=93 width=16) (actual time=269.025..271.016 rows=1 loops=1)

  • Filter: (user_id = 17355)
  • Rows Removed by Filter: 24010
51. 0.004 496.436 ↑ 93.0 1 1

Hash (cost=416.95..416.95 rows=93 width=36) (actual time=496.436..496.436 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 496.432 496.432 ↑ 93.0 1 1

CTE Scan on payments_summary ps (cost=0.00..416.95 rows=93 width=36) (actual time=494.344..496.432 rows=1 loops=1)

  • Filter: (user_id = 17355)
  • Rows Removed by Filter: 23139
53. 0.006 3,071.282 ↑ 151.0 1 1

Hash (cost=678.11..678.11 rows=151 width=28) (actual time=3,071.282..3,071.282 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 3,071.276 3,071.276 ↑ 151.0 1 1

CTE Scan on managed_accounts_summary mas (cost=0.00..678.11 rows=151 width=28) (actual time=3,062.117..3,071.276 rows=1 loops=1)

  • Filter: (user_id = 17355)
  • Rows Removed by Filter: 21246
Planning time : 1.516 ms
Execution time : 4,713.095 ms