explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8gvU

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.275 ↑ 1.0 1 1

Result (cost=10.85..10.86 rows=1 width=24) (actual time=0.275..0.275 rows=1 loops=1)

2.          

CTE ids

3. 0.002 0.046 ↑ 1.0 1 1

Limit (cost=0.00..0.01 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=1)

4. 0.044 0.044 ↑ 95,078,128.0 1 1

Seq Scan on spam (cost=0.00..1,371,481.28 rows=95,078,128 width=8) (actual time=0.044..0.044 rows=1 loops=1)

5.          

CTE cleanup_queue

6. 0.054 0.175 ↑ 1.0 1 1

Delete on spam spam_1 (cost=0.59..3.63 rows=1 width=38) (actual time=0.171..0.175 rows=1 loops=1)

7. 0.003 0.121 ↑ 1.0 1 1

Nested Loop (cost=0.59..3.63 rows=1 width=38) (actual time=0.119..0.121 rows=1 loops=1)

8. 0.008 0.074 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.073..0.074 rows=1 loops=1)

  • Group Key: ids.id
9. 0.066 0.066 ↑ 1.0 1 1

CTE Scan on ids (cost=0.00..0.02 rows=1 width=40) (actual time=0.064..0.066 rows=1 loops=1)

10. 0.044 0.044 ↑ 1.0 1 1

Index Scan using spam_id_idx on spam spam_1 (cost=0.57..3.58 rows=1 width=14) (actual time=0.043..0.044 rows=1 loops=1)

  • Index Cond: (id = ids.id)
11.          

CTE cleanup_thread

12. 0.001 0.030 ↓ 0.0 0 1

Delete on trade_replies_thread (cost=0.46..3.49 rows=1 width=38) (actual time=0.029..0.030 rows=0 loops=1)

13. 0.004 0.029 ↓ 0.0 0 1

Nested Loop (cost=0.46..3.49 rows=1 width=38) (actual time=0.029..0.029 rows=0 loops=1)

14. 0.004 0.008 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=1)

  • Group Key: ids_1.id
15. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on ids ids_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=1)

16. 0.017 0.017 ↓ 0.0 0 1

Index Scan using trade_replies_thread_created_with_reply_id_idx on trade_replies_thread (cost=0.43..3.45 rows=1 width=14) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: (created_with_reply_id = ids_1.id)
17.          

CTE cleanup_messages

18. 0.015 0.055 ↑ 1.0 1 1

Delete on trade_reply (cost=0.59..3.62 rows=1 width=38) (actual time=0.054..0.055 rows=1 loops=1)

19. 0.002 0.040 ↑ 1.0 1 1

Nested Loop (cost=0.59..3.62 rows=1 width=38) (actual time=0.039..0.040 rows=1 loops=1)

20. 0.003 0.006 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=1)

  • Group Key: ids_2.id
21. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on ids ids_2 (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.003 rows=1 loops=1)

22. 0.032 0.032 ↑ 1.0 1 1

Index Scan using pk_trade_reply on trade_reply (cost=0.57..3.58 rows=1 width=14) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: (id = ids_2.id)
23.          

Initplan (for Result)

24. 0.004 0.181 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.181..0.181 rows=1 loops=1)

25. 0.177 0.177 ↑ 1.0 1 1

CTE Scan on cleanup_queue (cost=0.00..0.02 rows=1 width=0) (actual time=0.173..0.177 rows=1 loops=1)

26. 0.002 0.032 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=1)

27. 0.030 0.030 ↓ 0.0 0 1

CTE Scan on cleanup_thread (cost=0.00..0.02 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1)

28. 0.002 0.058 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.058..0.058 rows=1 loops=1)

29. 0.056 0.056 ↑ 1.0 1 1

CTE Scan on cleanup_messages (cost=0.00..0.02 rows=1 width=0) (actual time=0.055..0.056 rows=1 loops=1)

Planning time : 0.927 ms
Execution time : 14,593.152 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fk_trade_replies_thread_created_with_reply_id_trade_reply on trade_reply 0.137 ms 1 0.137 ms
for constraint fk_trade_reply_parent_id_trade_reply on trade_reply 14,592.383 ms 1 14,592.383 ms