explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4JR

Settings
# exclusive inclusive rows x rows loops node
1. 467.882 467.882 ↑ 1,060.0 2 1

CTE Scan on s3r (cost=181,163.75..181,259.15 rows=2,120 width=189) (actual time=1.743..467.882 rows=2 loops=1)

  • Filter: ((q1 > 0) OR (q2 > 0))
  • Rows Removed by Filter: 1189
2.          

CTE us

3. 0.453 0.453 ↑ 1.0 1 1

Index Scan using users_pkey on users u (cost=0.14..3.17 rows=1 width=256) (actual time=0.452..0.453 rows=1 loops=1)

  • Index Cond: (id = 1)
4.          

CTE p

5. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on perspectives p (cost=0.00..1.20 rows=1 width=67) (actual time=0.017..0.018 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 19
6.          

CTE s1t

7. 0.032 0.055 ↑ 16.7 6 1

Nested Loop (cost=0.00..4.58 rows=100 width=4) (actual time=0.032..0.055 rows=6 loops=1)

  • Join Filter: (p_1.tags @> ARRAY[ut.id])
  • Rows Removed by Join Filter: 47
8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on p p_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

9. 0.022 0.022 ↓ 1.0 48 1

Seq Scan on task_user_tags ut (cost=0.00..3.47 rows=47 width=108) (actual time=0.007..0.022 rows=48 loops=1)

10.          

CTE s1a

11. 0.002 0.593 ↑ 8.5 6 1

Limit (cost=2.54..6.91 rows=51 width=35) (actual time=0.560..0.593 rows=6 loops=1)

12. 0.011 0.591 ↑ 8.5 6 1

Nested Loop (cost=2.54..6.91 rows=51 width=35) (actual time=0.558..0.591 rows=6 loops=1)

13. 0.002 0.544 ↓ 6.0 6 1

Nested Loop (cost=2.25..4.30 rows=1 width=4) (actual time=0.540..0.544 rows=6 loops=1)

14. 0.001 0.475 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.05 rows=1 width=0) (actual time=0.474..0.475 rows=1 loops=1)

15. 0.456 0.456 ↑ 1.0 1 1

CTE Scan on us (cost=0.00..0.02 rows=1 width=0) (actual time=0.455..0.456 rows=1 loops=1)

16. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on p p_2 (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)

17. 0.008 0.067 ↑ 16.7 6 1

HashAggregate (cost=2.25..3.25 rows=100 width=4) (actual time=0.065..0.067 rows=6 loops=1)

  • Group Key: s1t.tasks_id
18. 0.059 0.059 ↑ 16.7 6 1

CTE Scan on s1t (cost=0.00..2.00 rows=100 width=4) (actual time=0.032..0.059 rows=6 loops=1)

19. 0.036 0.036 ↑ 1.0 1 6

Index Scan using dev_pkey on task t (cost=0.29..2.60 rows=1 width=35) (actual time=0.006..0.006 rows=1 loops=6)

  • Index Cond: (id = s1t.tasks_id)
  • Filter: ((NOT draft) AND (schedule_id IS NULL) AND (user_id = 1))
20.          

CTE s3

21. 1.047 144.952 ↑ 3.2 1,191 1

Recursive Union (cost=0.00..2,712.10 rows=3,816 width=146) (actual time=0.182..144.952 rows=1,191 loops=1)

22. 1.606 1.606 ↓ 4.2 25 1

Seq Scan on task_folders tf (cost=0.00..69.22 rows=6 width=146) (actual time=0.180..1.606 rows=25 loops=1)

  • Filter: ((user_id = 1) AND (path[(array_length(path, 1) - 1)] = 299))
  • Rows Removed by Filter: 1951
23. 139.251 142.299 ↓ 1.0 389 3

Hash Join (cost=1.95..256.66 rows=381 width=146) (actual time=0.662..47.433 rows=389 loops=3)

  • Hash Cond: (tf_1.path[(array_length(tf_1.path, 1) - 1)] = s3.id)
24. 2.424 2.424 ↑ 1.0 1,243 3

Seq Scan on task_folders tf_1 (cost=0.00..51.50 rows=1,271 width=56) (actual time=0.016..0.808 rows=1,243 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 733
25. 0.267 0.624 ↓ 6.6 397 3

Hash (cost=1.20..1.20 rows=60 width=36) (actual time=0.208..0.208 rows=397 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
26. 0.357 0.357 ↓ 6.6 397 3

WorkTable Scan on s3 (cost=0.00..1.20 rows=60 width=36) (actual time=0.001..0.119 rows=397 loops=3)

27.          

CTE s3r

28. 151.338 465.762 ↑ 3.2 1,191 1

CTE Scan on s3 s3_1 (cost=0.00..178,435.80 rows=3,816 width=189) (actual time=0.955..465.762 rows=1,191 loops=1)

29.          

SubPlan (forCTE Scan)

30. 7.146 100.044 ↓ 0.0 0 1,191

Bitmap Heap Scan on task_folders tfi (cost=6.07..17.15 rows=3 width=0) (actual time=0.084..0.084 rows=0 loops=1,191)

  • Recheck Cond: (path @> ARRAY[s3_1.id])
  • Filter: (array_length(path, 1) > s3_1.depth)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1360
31. 92.898 92.898 ↑ 3.3 3 1,191

Bitmap Index Scan on task_folders_index01 (cost=0.00..6.07 rows=10 width=0) (actual time=0.078..0.078 rows=3 loops=1,191)

  • Index Cond: (path @> ARRAY[s3_1.id])
32. 2.382 177.459 ↑ 1.0 1 1,191

Aggregate (cost=18.48..18.49 rows=1 width=8) (actual time=0.149..0.149 rows=1 loops=1,191)

33. 5.955 175.077 ↓ 0.0 0 1,191

Hash Join (cost=17.25..18.47 rows=1 width=0) (actual time=0.147..0.147 rows=0 loops=1,191)

  • Hash Cond: (s1a.folder_id = tfi_1.id)
34. 2.382 2.382 ↑ 8.5 6 1,191

CTE Scan on s1a (cost=0.00..1.02 rows=51 width=4) (actual time=0.001..0.002 rows=6 loops=1,191)

35. 2.382 166.740 ↑ 3.3 3 1,191

Hash (cost=17.13..17.13 rows=10 width=4) (actual time=0.140..0.140 rows=3 loops=1,191)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 4.764 164.358 ↑ 3.3 3 1,191

Bitmap Heap Scan on task_folders tfi_1 (cost=6.08..17.13 rows=10 width=4) (actual time=0.136..0.138 rows=3 loops=1,191)

  • Recheck Cond: (path @> ARRAY[s3_1.id])
  • Filter: (id > 0)
  • Heap Blocks: exact=2410
37. 159.594 159.594 ↑ 3.3 3 1,191

Bitmap Index Scan on task_folders_index01 (cost=0.00..6.07 rows=10 width=0) (actual time=0.134..0.134 rows=3 loops=1,191)

  • Index Cond: (path @> ARRAY[s3_1.id])
38. 2.382 36.921 ↑ 1.0 1 1,191

Aggregate (cost=18.48..18.49 rows=1 width=8) (actual time=0.031..0.031 rows=1 loops=1,191)

39. 4.764 34.539 ↓ 0.0 0 1,191

Hash Join (cost=17.25..18.47 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=1,191)

  • Hash Cond: (s1a_1.folder_id = tfi_2.id)
40. 1.191 1.191 ↑ 8.5 6 1,191

CTE Scan on s1a s1a_1 (cost=0.00..1.02 rows=51 width=4) (actual time=0.000..0.001 rows=6 loops=1,191)

41. 2.382 28.584 ↑ 3.3 3 1,191

Hash (cost=17.13..17.13 rows=10 width=4) (actual time=0.024..0.024 rows=3 loops=1,191)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 4.764 26.202 ↑ 3.3 3 1,191

Bitmap Heap Scan on task_folders tfi_2 (cost=6.08..17.13 rows=10 width=4) (actual time=0.020..0.022 rows=3 loops=1,191)

  • Recheck Cond: (path @> ARRAY[s3_1.id])
  • Filter: (id > 0)
  • Heap Blocks: exact=2410
43. 21.438 21.438 ↑ 3.3 3 1,191

Bitmap Index Scan on task_folders_index01 (cost=0.00..6.07 rows=10 width=0) (actual time=0.018..0.018 rows=3 loops=1,191)

  • Index Cond: (path @> ARRAY[s3_1.id])