explain.depesz.com

PostgreSQL's explain analyze made readable

Result: maxc

Settings
# exclusive inclusive rows x rows loops node
1. 0.678 42.502 ↑ 1.0 1 1

Hash Join (cost=1,532.65..1,572.24 rows=1 width=834) (actual time=42.452..42.502 rows=1 loops=1)

  • Hash Cond: (c.id_category = t.id_category)
2. 0.086 0.086 ↑ 1.0 6 1

Seq Scan on category c (cost=0.00..2.06 rows=6 width=280) (actual time=0.020..0.086 rows=6 loops=1)

3. 0.081 40.923 ↑ 1.0 1 1

Hash (cost=1,532.63..1,532.63 rows=1 width=587) (actual time=40.915..40.923 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
4. 0.152 40.842 ↑ 1.0 1 1

Hash Join (cost=1,531.55..1,532.63 rows=1 width=587) (actual time=40.714..40.842 rows=1 loops=1)

  • Hash Cond: (u.id_user = t.id_user)
5. 0.100 0.100 ↓ 1.6 8 1

Seq Scan on "user" u (cost=0.00..1.05 rows=5 width=74) (actual time=0.015..0.100 rows=8 loops=1)

6. 0.040 40.590 ↑ 1.0 1 1

Hash (cost=1,531.54..1,531.54 rows=1 width=529) (actual time=40.583..40.590 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.044 40.550 ↑ 1.0 1 1

Nested Loop (cost=1,520.92..1,531.54 rows=1 width=529) (actual time=37.781..40.550 rows=1 loops=1)

  • Join Filter: (tms.id_thread = t_1.id_thread)
8. 0.041 40.408 ↑ 1.0 1 1

Merge Join (cost=1,520.79..1,531.07 rows=1 width=577) (actual time=37.657..40.408 rows=1 loops=1)

  • Merge Cond: (tms.id_thread = t_4.id_thread)
9. 0.490 8.091 ↑ 1.0 1 1

Nested Loop (cost=171.45..181.05 rows=1 width=557) (actual time=5.358..8.091 rows=1 loops=1)

  • Join Filter: (tms.id_thread = t.id_thread)
10. 0.607 6.741 ↓ 3.3 20 1

Merge Join (cost=171.32..176.70 rows=6 width=64) (actual time=5.125..6.741 rows=20 loops=1)

  • Merge Cond: (t_2.id_thread = tms.id_thread)
11. 0.407 3.187 ↑ 2.5 21 1

Sort (cost=70.51..70.64 rows=52 width=81) (actual time=3.004..3.187 rows=21 loops=1)

  • Sort Key: t_2.id_thread, ((tnu.* IS NOT NULL)) DESC, u_1.id_user
  • Sort Method: quicksort Memory: 27kB
12.          

CTE default_notification_frequency

13. 0.040 0.040 ↑ 4.0 1 1

Seq Scan on notification_frequency nf_1 (cost=0.00..21.00 rows=4 width=64) (actual time=0.015..0.040 rows=1 loops=1)

  • Filter: ((name)::text = 'immediate'::text)
  • Rows Removed by Filter: 1
14. 0.531 2.780 ↑ 2.5 21 1

Append (cost=24.57..48.03 rows=52 width=81) (actual time=1.078..2.780 rows=21 loops=1)

15. 0.525 2.249 ↑ 2.5 21 1

Nested Loop (cost=24.57..47.51 rows=52 width=81) (actual time=1.061..2.249 rows=21 loops=1)

16. 0.079 0.079 ↑ 4.0 1 1

CTE Scan on default_notification_frequency dnf (cost=0.00..0.08 rows=4 width=48) (actual time=0.035..0.079 rows=1 loops=1)

17. 0.447 1.645 ↓ 1.6 21 1

Materialize (cost=24.57..46.81 rows=13 width=168) (actual time=1.001..1.645 rows=21 loops=1)

18. 0.222 1.198 ↓ 1.6 21 1

Hash Right Join (cost=24.57..46.75 rows=13 width=168) (actual time=0.976..1.198 rows=21 loops=1)

  • Hash Cond: ((tnu.id_user = u_1.id_user) AND (tnu.id_thread = t_2.id_thread))
19. 0.062 0.174 ↑ 4.0 1 1

Hash Right Join (cost=21.05..43.19 rows=4 width=168) (actual time=0.139..0.174 rows=1 loops=1)

  • Hash Cond: (nf.id_notification_frequency = tnu.id_notification_frequency)
20. 0.035 0.035 ↑ 440.0 2 1

Seq Scan on notification_frequency nf (cost=0.00..18.80 rows=880 width=48) (actual time=0.011..0.035 rows=2 loops=1)

21. 0.033 0.077 ↑ 4.0 1 1

Hash (cost=21.00..21.00 rows=4 width=136) (actual time=0.070..0.077 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.044 0.044 ↑ 4.0 1 1

Seq Scan on thread_notify_user tnu (cost=0.00..21.00 rows=4 width=136) (actual time=0.021..0.044 rows=1 loops=1)

  • Filter: (id_user = 'be541e95-2ff3-49cd-b823-38cc40d8565d'::uuid)
23. 0.209 0.802 ↓ 1.6 21 1

Hash (cost=3.32..3.32 rows=13 width=32) (actual time=0.795..0.802 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.368 0.593 ↓ 1.6 21 1

Nested Loop (cost=0.00..3.32 rows=13 width=32) (actual time=0.051..0.593 rows=21 loops=1)

25. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on "user" u_1 (cost=0.00..1.06 rows=1 width=16) (actual time=0.013..0.029 rows=1 loops=1)

  • Filter: (id_user = 'be541e95-2ff3-49cd-b823-38cc40d8565d'::uuid)
  • Rows Removed by Filter: 7
26. 0.196 0.196 ↓ 1.6 21 1

Seq Scan on thread t_2 (cost=0.00..2.13 rows=13 width=16) (actual time=0.013..0.196 rows=21 loops=1)

27. 0.358 2.947 ↓ 3.3 20 1

Subquery Scan on tms (cost=100.81..105.34 rows=6 width=16) (actual time=2.090..2.947 rows=20 loops=1)

  • Filter: tms.is_visible
  • Rows Removed by Filter: 1
28. 0.357 2.589 ↓ 1.6 21 1

Unique (cost=100.81..105.21 rows=13 width=105) (actual time=2.072..2.589 rows=21 loops=1)

29. 0.478 2.232 ↑ 41.9 21 1

Sort (cost=100.81..103.01 rows=880 width=105) (actual time=2.055..2.232 rows=21 loops=1)

  • Sort Key: t_3.id_thread, mt.time_create DESC
  • Sort Method: quicksort Memory: 26kB
30. 0.682 1.754 ↑ 41.9 21 1

Hash Left Join (cost=32.09..57.77 rows=880 width=105) (actual time=0.861..1.754 rows=21 loops=1)

  • Hash Cond: (mt.id_moderation_action = ma.id_moderation_action)
31. 0.310 0.951 ↑ 41.9 21 1

Hash Right Join (cost=2.29..23.45 rows=880 width=40) (actual time=0.662..0.951 rows=21 loops=1)

  • Hash Cond: (mt.id_thread = t_3.id_thread)
32. 0.033 0.033 ↑ 880.0 1 1

Seq Scan on moderated_thread mt (cost=0.00..18.80 rows=880 width=40) (actual time=0.016..0.033 rows=1 loops=1)

33. 0.262 0.608 ↓ 1.6 21 1

Hash (cost=2.13..2.13 rows=13 width=16) (actual time=0.601..0.608 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.346 0.346 ↓ 1.6 21 1

Seq Scan on thread t_3 (cost=0.00..2.13 rows=13 width=16) (actual time=0.099..0.346 rows=21 loops=1)

35. 0.039 0.121 ↑ 440.0 2 1

Hash (cost=18.80..18.80 rows=880 width=48) (actual time=0.114..0.121 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.082 0.082 ↑ 440.0 2 1

Seq Scan on moderation_action ma (cost=0.00..18.80 rows=880 width=48) (actual time=0.016..0.082 rows=2 loops=1)

37. 0.860 0.860 ↓ 0.0 0 20

Index Scan using thread_pkey on thread t (cost=0.14..0.71 rows=1 width=493) (actual time=0.042..0.043 rows=0 loops=20)

  • Index Cond: (id_thread = t_2.id_thread)
  • Filter: (app.get_slug_from_fields((slug_prefix)::text, slug_suffix) = 'mencionando-en-el-frontpage'::text)
  • Rows Removed by Filter: 1
38. 0.039 32.276 ↑ 13.0 1 1

GroupAggregate (cost=1,349.33..1,349.84 rows=13 width=20) (actual time=32.268..32.276 rows=1 loops=1)

  • Group Key: t_4.id_thread
39. 0.040 32.237 ↑ 6.5 2 1

Merge Left Join (cost=1,349.33..1,349.55 rows=13 width=36) (actual time=32.202..32.237 rows=2 loops=1)

  • Merge Cond: (t_4.id_thread = p.id_thread)
40. 0.220 0.425 ↑ 6.5 2 1

Sort (cost=2.37..2.40 rows=13 width=16) (actual time=0.408..0.425 rows=2 loops=1)

  • Sort Key: t_4.id_thread
  • Sort Method: quicksort Memory: 25kB
41. 0.205 0.205 ↓ 1.6 21 1

Seq Scan on thread t_4 (cost=0.00..2.13 rows=13 width=16) (actual time=0.019..0.205 rows=21 loops=1)

42. 0.141 31.772 ↑ 10.0 1 1

Sort (cost=1,346.96..1,346.99 rows=10 width=20) (actual time=31.765..31.772 rows=1 loops=1)

  • Sort Key: p.id_thread
  • Sort Method: quicksort Memory: 25kB
43. 0.544 31.631 ↓ 1.2 12 1

Hash Join (cost=1,341.49..1,346.80 rows=10 width=20) (actual time=28.916..31.631 rows=12 loops=1)

  • Hash Cond: (p_1.id_post = p.id_post)
44. 0.931 30.806 ↑ 1.2 51 1

Hash Join (cost=1,338.77..1,343.30 rows=60 width=20) (actual time=28.497..30.806 rows=51 loops=1)

  • Hash Cond: (replies.id_root = p_1.id_post)
45.          

CTE replies

46. 1.934 27.986 ↑ 3.9 51 1

HashAggregate (cost=1,332.42..1,335.42 rows=200 width=20) (actual time=27.436..27.986 rows=51 loops=1)

  • Group Key: cte_1.id_root
47.          

CTE cte

48. 2.687 23.470 ↑ 2.6 154 1

Recursive Union (cost=105.21..1,322.42 rows=400 width=32) (actual time=5.177..23.470 rows=154 loops=1)

49. 0.934 8.453 ↓ 1.7 51 1

Hash Join (cost=105.21..110.29 rows=30 width=32) (actual time=5.159..8.453 rows=51 loops=1)

  • Hash Cond: (pms1.id_post = p1.id_post)
50. 0.898 6.125 ↓ 1.7 51 1

Subquery Scan on pms1 (cost=101.86..106.86 rows=30 width=16) (actual time=3.730..6.125 rows=51 loops=1)

  • Filter: pms1.is_visible
  • Rows Removed by Filter: 4
51. 1.071 5.227 ↑ 1.1 55 1

Unique (cost=101.86..106.26 rows=60 width=73) (actual time=3.713..5.227 rows=55 loops=1)

52. 1.068 4.156 ↑ 16.0 55 1

Sort (cost=101.86..104.06 rows=880 width=73) (actual time=3.696..4.156 rows=55 loops=1)

  • Sort Key: p_2.id_post, mp.time_create DESC
  • Sort Method: quicksort Memory: 29kB
53. 1.187 3.088 ↑ 16.0 55 1

Hash Left Join (cost=33.15..58.83 rows=880 width=73) (actual time=1.303..3.088 rows=55 loops=1)

  • Hash Cond: (mp.id_moderation_action = ma_1.id_moderation_action)
54. 0.640 1.822 ↑ 16.0 55 1

Hash Right Join (cost=3.35..24.50 rows=880 width=40) (actual time=1.184..1.822 rows=55 loops=1)

  • Hash Cond: (mp.id_post = p_2.id_post)
55. 0.056 0.056 ↑ 220.0 4 1

Seq Scan on moderated_post mp (cost=0.00..18.80 rows=880 width=40) (actual time=0.016..0.056 rows=4 loops=1)

56. 0.633 1.126 ↑ 1.1 55 1

Hash (cost=2.60..2.60 rows=60 width=16) (actual time=1.119..1.126 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
57. 0.493 0.493 ↑ 1.1 55 1

Seq Scan on post p_2 (cost=0.00..2.60 rows=60 width=16) (actual time=0.017..0.493 rows=55 loops=1)

58. 0.042 0.079 ↑ 440.0 2 1

Hash (cost=18.80..18.80 rows=880 width=48) (actual time=0.071..0.079 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.037 0.037 ↑ 440.0 2 1

Seq Scan on moderation_action ma_1 (cost=0.00..18.80 rows=880 width=48) (actual time=0.012..0.037 rows=2 loops=1)

60. 0.651 1.394 ↑ 1.1 55 1

Hash (cost=2.60..2.60 rows=60 width=16) (actual time=1.386..1.394 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
61. 0.743 0.743 ↑ 1.1 55 1

Seq Scan on post p1 (cost=0.00..2.60 rows=60 width=16) (actual time=0.012..0.743 rows=55 loops=1)

62. 2.294 12.330 ↑ 3.7 10 10

Hash Join (cost=110.67..120.41 rows=37 width=32) (actual time=0.908..1.233 rows=10 loops=10)

  • Hash Cond: (cte.id_post = p2.id_parent)
63. 1.340 1.340 ↑ 20.0 15 10

WorkTable Scan on cte (cost=0.00..6.00 rows=300 width=32) (actual time=0.008..0.134 rows=15 loops=10)

64. 0.516 8.696 ↓ 1.3 39 1

Hash (cost=110.29..110.29 rows=30 width=32) (actual time=8.689..8.696 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
65. 0.996 8.180 ↓ 1.7 51 1

Hash Join (cost=105.21..110.29 rows=30 width=32) (actual time=4.315..8.180 rows=51 loops=1)

  • Hash Cond: (pms2.id_post = p2.id_post)
66. 0.989 6.167 ↓ 1.7 51 1

Subquery Scan on pms2 (cost=101.86..106.86 rows=30 width=16) (actual time=3.256..6.167 rows=51 loops=1)

  • Filter: pms2.is_visible
  • Rows Removed by Filter: 4
67. 1.492 5.178 ↑ 1.1 55 1

Unique (cost=101.86..106.26 rows=60 width=73) (actual time=3.232..5.178 rows=55 loops=1)

68. 1.068 3.686 ↑ 16.0 55 1

Sort (cost=101.86..104.06 rows=880 width=73) (actual time=3.212..3.686 rows=55 loops=1)

  • Sort Key: p_3.id_post, mp_1.time_create DESC
  • Sort Method: quicksort Memory: 29kB
69. 1.019 2.618 ↑ 16.0 55 1

Hash Left Join (cost=33.15..58.83 rows=880 width=73) (actual time=1.043..2.618 rows=55 loops=1)

  • Hash Cond: (mp_1.id_moderation_action = ma_2.id_moderation_action)
70. 0.575 1.527 ↑ 16.0 55 1

Hash Right Join (cost=3.35..24.50 rows=880 width=40) (actual time=0.943..1.527 rows=55 loops=1)

  • Hash Cond: (mp_1.id_post = p_3.id_post)
71. 0.048 0.048 ↑ 220.0 4 1

Seq Scan on moderated_post mp_1 (cost=0.00..18.80 rows=880 width=40) (actual time=0.011..0.048 rows=4 loops=1)

72. 0.459 0.904 ↑ 1.1 55 1

Hash (cost=2.60..2.60 rows=60 width=16) (actual time=0.896..0.904 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
73. 0.445 0.445 ↑ 1.1 55 1

Seq Scan on post p_3 (cost=0.00..2.60 rows=60 width=16) (actual time=0.011..0.445 rows=55 loops=1)

74. 0.037 0.072 ↑ 440.0 2 1

Hash (cost=18.80..18.80 rows=880 width=48) (actual time=0.065..0.072 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 0.035 0.035 ↑ 440.0 2 1

Seq Scan on moderation_action ma_2 (cost=0.00..18.80 rows=880 width=48) (actual time=0.011..0.035 rows=2 loops=1)

76. 0.503 1.017 ↑ 1.1 55 1

Hash (cost=2.60..2.60 rows=60 width=32) (actual time=1.009..1.017 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
77. 0.514 0.514 ↑ 1.1 55 1

Seq Scan on post p2 (cost=0.00..2.60 rows=60 width=32) (actual time=0.039..0.514 rows=55 loops=1)

78. 26.052 26.052 ↑ 2.6 154 1

CTE Scan on cte cte_1 (cost=0.00..8.00 rows=400 width=32) (actual time=5.195..26.052 rows=154 loops=1)

79. 28.877 28.877 ↑ 3.9 51 1

CTE Scan on replies (cost=0.00..4.00 rows=200 width=20) (actual time=27.453..28.877 rows=51 loops=1)

80. 0.512 0.998 ↑ 1.1 55 1

Hash (cost=2.60..2.60 rows=60 width=16) (actual time=0.990..0.998 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
81. 0.486 0.486 ↑ 1.1 55 1

Seq Scan on post p_1 (cost=0.00..2.60 rows=60 width=16) (actual time=0.019..0.486 rows=55 loops=1)

82. 0.138 0.281 ↓ 1.3 13 1

Hash (cost=2.60..2.60 rows=10 width=32) (actual time=0.273..0.281 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.143 0.143 ↓ 1.3 13 1

Seq Scan on post p (cost=0.00..2.60 rows=10 width=32) (actual time=0.016..0.143 rows=13 loops=1)

  • Filter: (id_parent IS NULL)
  • Rows Removed by Filter: 42
84. 0.098 0.098 ↑ 1.0 1 1

Index Only Scan using thread_pkey on thread t_1 (cost=0.14..0.46 rows=1 width=16) (actual time=0.091..0.098 rows=1 loops=1)

  • Index Cond: (id_thread = t_2.id_thread)
  • Heap Fetches: 1
85.          

SubPlan (for Hash Join)

86. 0.122 0.514 ↑ 6.0 1 1

GroupAggregate (cost=5.31..14.99 rows=6 width=48) (actual time=0.499..0.514 rows=1 loops=1)

  • Group Key: tmu.id_thread
87. 0.106 0.392 ↑ 6.0 1 1

Hash Join (cost=5.31..14.80 rows=6 width=78) (actual time=0.358..0.392 rows=1 loops=1)

  • Hash Cond: (tmu.id_user = u_2.id_user)
88. 0.040 0.072 ↑ 6.0 1 1

Bitmap Heap Scan on thread_mention_user tmu (cost=4.20..13.67 rows=6 width=36) (actual time=0.056..0.072 rows=1 loops=1)

  • Recheck Cond: (id_thread = t_1.id_thread)
  • Heap Blocks: exact=1
89. 0.032 0.032 ↑ 6.0 1 1

Bitmap Index Scan on thread_mention_user_pkey (cost=0.00..4.20 rows=6 width=0) (actual time=0.024..0.032 rows=1 loops=1)

  • Index Cond: (id_thread = t_1.id_thread)
90. 0.104 0.214 ↓ 1.6 8 1

Hash (cost=1.05..1.05 rows=5 width=74) (actual time=0.207..0.214 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.110 0.110 ↓ 1.6 8 1

Seq Scan on "user" u_2 (cost=0.00..1.05 rows=5 width=74) (actual time=0.032..0.110 rows=8 loops=1)

92. 0.021 0.301 ↓ 0.0 0 1

GroupAggregate (cost=6.33..22.01 rows=6 width=48) (actual time=0.294..0.301 rows=0 loops=1)

  • Group Key: tmc.id_thread
93. 0.051 0.280 ↓ 0.0 0 1

Hash Join (cost=6.33..15.82 rows=6 width=36) (actual time=0.273..0.280 rows=0 loops=1)

  • Hash Cond: (tmc.id_category = c_1.id_category)
94. 0.021 0.046 ↓ 0.0 0 1

Bitmap Heap Scan on thread_mention_category tmc (cost=4.20..13.67 rows=6 width=36) (actual time=0.038..0.046 rows=0 loops=1)

  • Recheck Cond: (id_thread = t_1.id_thread)
95. 0.025 0.025 ↓ 0.0 0 1

Bitmap Index Scan on thread_mention_category_pkey (cost=0.00..4.20 rows=6 width=0) (actual time=0.017..0.025 rows=0 loops=1)

  • Index Cond: (id_thread = t_1.id_thread)
96. 0.082 0.183 ↑ 1.0 6 1

Hash (cost=2.06..2.06 rows=6 width=32) (actual time=0.176..0.183 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
97. 0.101 0.101 ↑ 1.0 6 1

Seq Scan on category c_1 (cost=0.00..2.06 rows=6 width=32) (actual time=0.025..0.101 rows=6 loops=1)

Planning time : 5.019 ms
Execution time : 44.837 ms