#	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)

                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4846613.92..4846613.93 rows=1 width=16) (actual time=851367.046..851367.046 rows=1 loops=1)
   CTE new_spam_messages
     ->  Update on trade_reply  (cost=0.00..1928389.78 rows=23966370 width=140) (actual time=0.081..779935.836 rows=14876284 loops=1)
           ->  Seq Scan on trade_reply  (cost=0.00..1928389.78 rows=23966370 width=140) (actual time=0.017..64098.698 rows=14876284 loops=1)
                 Filter: ((hidden IS DISTINCT FROM true) AND (message = 'Go trade https://csgogem.trade/'::text))
                 Rows Removed by Filter: 24291545
   CTE fix_childs
     ->  Update on trade_reply trade_reply_1  (cost=539247.83..2378743.98 rows=10525 width=172) (actual time=30908.822..60697.880 rows=310 loops=1)
           ->  Hash Join  (cost=539247.83..2378743.98 rows=10525 width=172) (actual time=30907.360..60497.290 rows=310 loops=1)
                 Hash Cond: (trade_reply_1.parent_id = new_spam_messages.id)
                 ->  Seq Scan on trade_reply trade_reply_1  (cost=0.00..1775623.65 rows=24287777 width=139) (actual time=0.020..33520.594 rows=15427071 loops=1)
                       Filter: (hidden IS DISTINCT FROM true)
                       Rows Removed by Filter: 23740758
                 ->  Hash  (cost=539245.33..539245.33 rows=200 width=40) (actual time=23470.796..23470.796 rows=14876284 loops=1)
                       Buckets: 16777216 (originally 1024)  Batches: 2 (originally 1)  Memory Usage: 917505kB
                       ->  HashAggregate  (cost=539243.33..539245.33 rows=200 width=40) (actual time=12256.103..18063.533 rows=14876284 loops=1)
                             Group Key: new_spam_messages.id
                             ->  CTE Scan on new_spam_messages  (cost=0.00..479327.40 rows=23966370 width=40) (actual time=0.007..3865.899 rows=14876284 loops=1)
   InitPlan 3 (returns $4)
     ->  Aggregate  (cost=539243.33..539243.34 rows=1 width=8) (actual time=790668.272..790668.272 rows=1 loops=1)
           ->  CTE Scan on new_spam_messages new_spam_messages_1  (cost=0.00..479327.40 rows=23966370 width=0) (actual time=0.082..787565.765 rows=14876284 loops=1)
   InitPlan 4 (returns $5)
     ->  Aggregate  (cost=236.81..236.82 rows=1 width=8) (actual time=60698.765..60698.765 rows=1 loops=1)
           ->  CTE Scan on fix_childs  (cost=0.00..210.50 rows=10525 width=0) (actual time=30908.832..60698.373 rows=310 loops=1)
 Planning time: 0.250 ms
 Execution time: 851677.788 ms
(26 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	3,102.899 ms	0.4 %
CTE Scan	3	852,130.037 ms	100.1 %
Hash	1	5,407.263 ms	0.6 %
Hash Join	1	3,505.900 ms	0.4 %
HashAggregate	1	14,197.634 ms	1.7 %
Result	1	0.009 ms	0.0 %
Seq Scan	2	97,619.292 ms	11.5 %
Update	2	716,037.728 ms	84.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
trade_reply	4	813,657.020 ms	95.6 %
Seq Scan	2	97,619.292 ms	12.0 %
Update	2	716,037.728 ms	88.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UiLC

Color mode:

Visible columns:

Per node type stats

Per table stats