#	exclusive	inclusive	rows x	rows	loops	node
1.	2248.848	13404.220	↓ 1.0	4940187	1	→ WindowAgg (cost=1386642.46..1484952.48 rows=4915501 width=12) (actual time=10761.814..13404.220 rows=4940187 loops=1)
2.						CTE ranking
3.	2635.905	7076.647	↓ 1.0	4940187	1	→ WindowAgg (cost=655980.09..742001.36 rows=4915501 width=22) (actual time=3601.696..7076.647 rows=4940187 loops=1)
4.	3568.626	4440.742	↓ 1.0	4940187	1	→ Sort (cost=655980.09..668268.85 rows=4915501 width=22) (actual time=3601.684..4440.742 rows=4940187 loops=1) Sort Key: tmp_cluster_de.cid Sort Method: quicksort Memory: 582561kB
5.	872.116	872.116	↓ 1.0	4940187	1	→ Seq Scan on tmp_cluster_de (cost=0.00..109649.02 rows=4915501 width=22) (actual time=0.009..872.116 rows=4940187 loops=1) Filter: (cid IS NOT NULL)
6.	2611.138	11155.372	↓ 1.0	4940187	1	→ Sort (cost=644641.09..656929.85 rows=4915501 width=12) (actual time=10761.804..11155.372 rows=4940187 loops=1) Sort Key: ranking.sub_cluster_id, ranking.cluster_id Sort Method: quicksort Memory: 428180kB
7.	8544.234	8544.234	↓ 1.0	4940187	1	→ CTE Scan on ranking (cost=0.00..98310.02 rows=4915501 width=12) (actual time=3601.699..8544.234 rows=4940187 loops=1)

WindowAgg  (cost=1386642.46..1484952.48 rows=4915501 width=12) (actual time=10761.814..13404.220 rows=4940187 loops=1)
  CTE ranking
    ->  WindowAgg  (cost=655980.09..742001.36 rows=4915501 width=22) (actual time=3601.696..7076.647 rows=4940187 loops=1)
          ->  Sort  (cost=655980.09..668268.85 rows=4915501 width=22) (actual time=3601.684..4440.742 rows=4940187 loops=1)
                Sort Key: tmp_cluster_de.cid
                Sort Method: quicksort  Memory: 582561kB
                ->  Seq Scan on tmp_cluster_de  (cost=0.00..109649.02 rows=4915501 width=22) (actual time=0.009..872.116 rows=4940187 loops=1)
                      Filter: (cid IS NOT NULL)
  ->  Sort  (cost=644641.09..656929.85 rows=4915501 width=12) (actual time=10761.804..11155.372 rows=4940187 loops=1)
        Sort Key: ranking.sub_cluster_id, ranking.cluster_id
        Sort Method: quicksort  Memory: 428180kB
        ->  CTE Scan on ranking  (cost=0.00..98310.02 rows=4915501 width=12) (actual time=3601.699..8544.234 rows=4940187 loops=1)
Total runtime: 15246.316 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	1	8544.234 ms	63.7 %
Seq Scan	1	872.116 ms	6.5 %
Sort	2	6179.764 ms	46.1 %
WindowAgg	2	4884.753 ms	36.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
tmp_cluster_de	1	872.116 ms	6.5 %
Seq Scan	1	872.116 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1p8

Colorize:

Columns visibility:

Per node type stats

Per table stats