#	exclusive	inclusive	rows x	rows	loops	node
1.	2.173	2.173	↑ 200.0	50	1	→ CTE Scan on with_data (cost=535.05..587.30 rows=10000 width=32) (actual time=0.230..2.173 rows=50 loops=1)
2.						CTE skip
3.	0.018	1.161	↑ 9.2	11	1	→ Recursive Union (cost=0.00..54.11 rows=101 width=32) (actual time=0.046..1.161 rows=11 loops=1)
4.	0.001	0.043	↑ 1.0	1	1	→ Limit (cost=0.00..0.44 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)
5.	0.042	0.042	↑ 2000000.0	1	1	→ Index Only Scan using iu on test t (cost=0.00..888825.76 rows=2000000 width=8) (actual time=0.042..0.042 rows=1 loops=1) Heap Fetches: 1
6.	0.020	1.100	↑ 10.0	1	11	→ WorkTable Scan on skip s (cost=0.00..5.16 rows=10 width=32) (actual time=0.100..0.100 rows=1 loops=11) Filter: (username IS NOT NULL) Rows Removed by Filter: 0
7.						SubPlan (forWorkTable Scan on skip s)
8.	0.020	1.080	↑ 1.0	1	10	→ Result (cost=0.49..0.50 rows=1 width=0) (actual time=0.108..0.108 rows=1 loops=10)
9.						Initplan (forResult)
10.	0.010	1.060	↑ 1.0	1	10	→ Limit (cost=0.00..0.49 rows=1 width=8) (actual time=0.106..0.106 rows=1 loops=10)
11.	1.050	1.050	↑ 666667.0	1	10	→ Index Only Scan using iu on test t2 (cost=0.00..324286.77 rows=666667 width=8) (actual time=0.105..0.105 rows=1 loops=10) Index Cond: ((username IS NOT NULL) AND (username > s.username)) Heap Fetches: 9
12.						CTE with_data
13.	1.246	2.066	↑ 10.0	10	1	→ CTE Scan on skip s_1 (cost=0.00..480.95 rows=100 width=32) (actual time=0.201..2.066 rows=10 loops=1) Filter: (username IS NOT NULL) Rows Removed by Filter: 1
14.						SubPlan (forCTE Scan)
15.	0.020	0.820	↑ 1.0	5	10	→ Limit (cost=0.00..4.79 rows=5 width=52) (actual time=0.050..0.082 rows=5 loops=10)
16.	0.800	0.800	↑ 40000.0	5	10	→ Index Scan Backward using i on test t_1 (cost=0.00..191570.45 rows=200000 width=52) (actual time=0.049..0.080 rows=5 loops=10) Index Cond: (username = s_1.username)

                                                                           QUERY PLAN                                                                           
ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ
 CTE Scan on with_data  (cost=535.05..587.30 rows=10000 width=32) (actual time=0.230..2.173 rows=50 loops=1)
   CTE skip
     ->  Recursive Union  (cost=0.00..54.11 rows=101 width=32) (actual time=0.046..1.161 rows=11 loops=1)
           ->  Limit  (cost=0.00..0.44 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)
                 ->  Index Only Scan using iu on test t  (cost=0.00..888825.76 rows=2000000 width=8) (actual time=0.042..0.042 rows=1 loops=1)
                       Heap Fetches: 1
           ->  WorkTable Scan on skip s  (cost=0.00..5.16 rows=10 width=32) (actual time=0.100..0.100 rows=1 loops=11)
                 Filter: (username IS NOT NULL)
                 Rows Removed by Filter: 0
                 SubPlan 2
                   ->  Result  (cost=0.49..0.50 rows=1 width=0) (actual time=0.108..0.108 rows=1 loops=10)
                         InitPlan 1 (returns $2)
                           ->  Limit  (cost=0.00..0.49 rows=1 width=8) (actual time=0.106..0.106 rows=1 loops=10)
                                 ->  Index Only Scan using iu on test t2  (cost=0.00..324286.77 rows=666667 width=8) (actual time=0.105..0.105 rows=1 loops=10)
                                       Index Cond: ((username IS NOT NULL) AND (username > s.username))
                                       Heap Fetches: 9
   CTE with_data
     ->  CTE Scan on skip s_1  (cost=0.00..480.95 rows=100 width=32) (actual time=0.201..2.066 rows=10 loops=1)
           Filter: (username IS NOT NULL)
           Rows Removed by Filter: 1
           SubPlan 4
             ->  Limit  (cost=0.00..4.79 rows=5 width=52) (actual time=0.050..0.082 rows=5 loops=10)
                   ->  Index Scan Backward using i on test t_1  (cost=0.00..191570.45 rows=200000 width=52) (actual time=0.049..0.080 rows=5 loops=10)
                         Index Cond: (username = s_1.username)
 Total runtime: 2.309 ms

Per node type stats

node type	count	sum of times	% of query
CTE Scan	2	3.419 ms	157.3 %
Index Only Scan using iu on test t	1	0.042 ms	1.9 %
Index Only Scan using iu on test t2	1	1.050 ms	48.3 %
Index Scan Backward	1	0.800 ms	36.8 %
Limit	3	0.031 ms	1.4 %
Recursive Union	1	0.018 ms	0.8 %
Result	1	0.020 ms	0.9 %
WorkTable Scan on skip s	1	0.020 ms	0.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
test	1	0.800 ms	36.8 %
Index Scan Backward	1	0.800 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: GKB

Colorize:

Columns visibility:

Per node type stats

Per table stats