explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UiLC

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 851,367.046 ↑ 1.0 1 1

Result (cost=4,846,613.92..4,846,613.93 rows=1 width=16) (actual time=851,367.046..851,367.046 rows=1 loops=1)

2.          

CTE new_spam_messages

3. 715,837.138 779,935.836 ↑ 1.6 14,876,284 1

Update on trade_reply (cost=0.00..1,928,389.78 rows=23,966,370 width=140) (actual time=0.081..779,935.836 rows=14,876,284 loops=1)

4. 64,098.698 64,098.698 ↑ 1.6 14,876,284 1

Seq Scan on trade_reply (cost=0.00..1,928,389.78 rows=23,966,370 width=140) (actual time=0.017..64,098.698 rows=14,876,284 loops=1)

  • Filter: ((hidden IS DISTINCT FROM true) AND (message = 'Go trade https://csgogem.trade/'::text))
  • Rows Removed by Filter: 24291545
5.          

CTE fix_childs

6. 200.590 60,697.880 ↑ 34.0 310 1

Update on trade_reply trade_reply_1 (cost=539,247.83..2,378,743.98 rows=10,525 width=172) (actual time=30,908.822..60,697.880 rows=310 loops=1)

7. 3,505.900 60,497.290 ↑ 34.0 310 1

Hash Join (cost=539,247.83..2,378,743.98 rows=10,525 width=172) (actual time=30,907.360..60,497.290 rows=310 loops=1)

  • Hash Cond: (trade_reply_1.parent_id = new_spam_messages.id)
8. 33,520.594 33,520.594 ↑ 1.6 15,427,071 1

Seq Scan on trade_reply trade_reply_1 (cost=0.00..1,775,623.65 rows=24,287,777 width=139) (actual time=0.020..33,520.594 rows=15,427,071 loops=1)

  • Filter: (hidden IS DISTINCT FROM true)
  • Rows Removed by Filter: 23740758
9. 5,407.263 23,470.796 ↓ 74,381.4 14,876,284 1

Hash (cost=539,245.33..539,245.33 rows=200 width=40) (actual time=23,470.796..23,470.796 rows=14,876,284 loops=1)

  • Buckets: 16777216 (originally 1024) Batches: 2 (originally 1) Memory Usage: 917505kB
10. 14,197.634 18,063.533 ↓ 74,381.4 14,876,284 1

HashAggregate (cost=539,243.33..539,245.33 rows=200 width=40) (actual time=12,256.103..18,063.533 rows=14,876,284 loops=1)

  • Group Key: new_spam_messages.id
11. 3,865.899 3,865.899 ↑ 1.6 14,876,284 1

CTE Scan on new_spam_messages (cost=0.00..479,327.40 rows=23,966,370 width=40) (actual time=0.007..3,865.899 rows=14,876,284 loops=1)

12.          

Initplan (forResult)

13. 3,102.507 790,668.272 ↑ 1.0 1 1

Aggregate (cost=539,243.33..539,243.34 rows=1 width=8) (actual time=790,668.272..790,668.272 rows=1 loops=1)

14. 787,565.765 787,565.765 ↑ 1.6 14,876,284 1

CTE Scan on new_spam_messages new_spam_messages_1 (cost=0.00..479,327.40 rows=23,966,370 width=0) (actual time=0.082..787,565.765 rows=14,876,284 loops=1)

15. 0.392 60,698.765 ↑ 1.0 1 1

Aggregate (cost=236.81..236.82 rows=1 width=8) (actual time=60,698.765..60,698.765 rows=1 loops=1)

16. 60,698.373 60,698.373 ↑ 34.0 310 1

CTE Scan on fix_childs (cost=0.00..210.50 rows=10,525 width=0) (actual time=30,908.832..60,698.373 rows=310 loops=1)