explain.depesz.com

A tool for finding a real cause for slow queries.

Result: GKB

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# 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)