explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X7Ti

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 73.321 ↑ 1.0 1 1

Result (cost=1,082.87..1,082.88 rows=1 width=24) (actual time=73.320..73.321 rows=1 loops=1)

2.          

CTE ids

3. 0.019 3.038 ↑ 1.0 100 1

Limit (cost=0.00..1.44 rows=100 width=8) (actual time=3.006..3.038 rows=100 loops=1)

4. 3.019 3.019 ↑ 950,781.3 100 1

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

5.          

CTE cleanup_queue

6. 0.534 12.252 ↑ 1.0 100 1

Delete on spam spam_1 (cost=2.82..362.75 rows=100 width=38) (actual time=10.945..12.252 rows=100 loops=1)

7. 0.182 11.718 ↑ 1.0 100 1

Nested Loop (cost=2.82..362.75 rows=100 width=38) (actual time=10.805..11.718 rows=100 loops=1)

8. 0.206 3.436 ↑ 1.0 100 1

HashAggregate (cost=2.25..3.25 rows=100 width=40) (actual time=3.354..3.436 rows=100 loops=1)

  • Group Key: ids.id
9. 3.230 3.230 ↑ 1.0 100 1

CTE Scan on ids (cost=0.00..2.00 rows=100 width=40) (actual time=3.112..3.230 rows=100 loops=1)

10. 8.100 8.100 ↑ 1.0 1 100

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

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

CTE cleanup_thread

12. 0.003 0.855 ↓ 0.0 0 1

Delete on trade_replies_thread (cost=2.68..349.25 rows=140 width=38) (actual time=0.855..0.855 rows=0 loops=1)

13. 0.083 0.852 ↓ 0.0 0 1

Nested Loop (cost=2.68..349.25 rows=140 width=38) (actual time=0.852..0.852 rows=0 loops=1)

14. 0.175 0.269 ↑ 1.0 100 1

HashAggregate (cost=2.25..3.25 rows=100 width=40) (actual time=0.211..0.269 rows=100 loops=1)

  • Group Key: ids_1.id
15. 0.094 0.094 ↑ 1.0 100 1

CTE Scan on ids ids_1 (cost=0.00..2.00 rows=100 width=40) (actual time=0.043..0.094 rows=100 loops=1)

16. 0.500 0.500 ↓ 0.0 0 100

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.005..0.005 rows=0 loops=100)

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

CTE cleanup_messages

18. 16.941 59.818 ↑ 1.0 100 1

Delete on trade_reply (cost=2.82..361.75 rows=100 width=38) (actual time=7.860..59.818 rows=100 loops=1)

19. 0.321 42.877 ↑ 1.0 100 1

Nested Loop (cost=2.82..361.75 rows=100 width=38) (actual time=7.172..42.877 rows=100 loops=1)

20. 0.278 0.356 ↑ 1.0 100 1

HashAggregate (cost=2.25..3.25 rows=100 width=40) (actual time=0.196..0.356 rows=100 loops=1)

  • Group Key: ids_2.id
21. 0.078 0.078 ↑ 1.0 100 1

CTE Scan on ids ids_2 (cost=0.00..2.00 rows=100 width=40) (actual time=0.032..0.078 rows=100 loops=1)

22. 42.200 42.200 ↑ 1.0 1 100

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

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

Initplan (for Result)

24. 0.041 12.357 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=8) (actual time=12.357..12.357 rows=1 loops=1)

25. 12.316 12.316 ↑ 1.0 100 1

CTE Scan on cleanup_queue (cost=0.00..2.00 rows=100 width=0) (actual time=10.950..12.316 rows=100 loops=1)

26. 0.004 0.861 ↑ 1.0 1 1

Aggregate (cost=3.15..3.16 rows=1 width=8) (actual time=0.861..0.861 rows=1 loops=1)

27. 0.857 0.857 ↓ 0.0 0 1

CTE Scan on cleanup_thread (cost=0.00..2.80 rows=140 width=0) (actual time=0.857..0.857 rows=0 loops=1)

28. 0.110 60.090 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=8) (actual time=60.090..60.090 rows=1 loops=1)

29. 59.980 59.980 ↑ 1.0 100 1

CTE Scan on cleanup_messages (cost=0.00..2.00 rows=100 width=0) (actual time=7.864..59.980 rows=100 loops=1)

Planning time : 38.591 ms
Execution time : 96.899 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 10.714 ms 100 0.107 ms
for constraint fk_trade_reply_parent_id_trade_reply on trade_reply 10.095 ms 100 0.101 ms