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

QUERY PLAN
CTE Scan on s3r  (cost=181163.75..181259.15 rows=2120 width=189) (actual time=1.743..467.882 rows=2 loops=1)	
  Filter: ((q1 > 0) OR (q2 > 0))	
  Rows Removed by Filter: 1189	
  CTE us	
    ->  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)	
  CTE p	
    ->  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	
  CTE s1t	
    ->  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	
          ->  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)	
          ->  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)	
  CTE s1a	
    ->  Limit  (cost=2.54..6.91 rows=51 width=35) (actual time=0.560..0.593 rows=6 loops=1)	
          ->  Nested Loop  (cost=2.54..6.91 rows=51 width=35) (actual time=0.558..0.591 rows=6 loops=1)	
                ->  Nested Loop  (cost=2.25..4.30 rows=1 width=4) (actual time=0.540..0.544 rows=6 loops=1)	
                      ->  Nested Loop  (cost=0.00..0.05 rows=1 width=0) (actual time=0.474..0.475 rows=1 loops=1)	
                            ->  CTE Scan on us  (cost=0.00..0.02 rows=1 width=0) (actual time=0.455..0.456 rows=1 loops=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)	
                      ->  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	
                            ->  CTE Scan on s1t  (cost=0.00..2.00 rows=100 width=4) (actual time=0.032..0.059 rows=6 loops=1)	
                ->  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))	
  CTE s3	
    ->  Recursive Union  (cost=0.00..2712.10 rows=3816 width=146) (actual time=0.182..144.952 rows=1191 loops=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	
          ->  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)	
                ->  Seq Scan on task_folders tf_1  (cost=0.00..51.50 rows=1271 width=56) (actual time=0.016..0.808 rows=1243 loops=3)	
                      Filter: (user_id = 1)	
                      Rows Removed by Filter: 733	
                ->  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	
                      ->  WorkTable Scan on s3  (cost=0.00..1.20 rows=60 width=36) (actual time=0.001..0.119 rows=397 loops=3)	
  CTE s3r	
    ->  CTE Scan on s3 s3_1  (cost=0.00..178435.80 rows=3816 width=189) (actual time=0.955..465.762 rows=1191 loops=1)	
          SubPlan 6	
            ->  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=1191)	
                  Recheck Cond: (path @> ARRAY[s3_1.id])	
                  Filter: (array_length(path, 1) > s3_1.depth)	
                  Rows Removed by Filter: 1	
                  Heap Blocks: exact=1360	
                  ->  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=1191)	
                        Index Cond: (path @> ARRAY[s3_1.id])	
          SubPlan 7	
            ->  Aggregate  (cost=18.48..18.49 rows=1 width=8) (actual time=0.149..0.149 rows=1 loops=1191)	
                  ->  Hash Join  (cost=17.25..18.47 rows=1 width=0) (actual time=0.147..0.147 rows=0 loops=1191)	
                        Hash Cond: (s1a.folder_id = tfi_1.id)	
                        ->  CTE Scan on s1a  (cost=0.00..1.02 rows=51 width=4) (actual time=0.001..0.002 rows=6 loops=1191)	
                        ->  Hash  (cost=17.13..17.13 rows=10 width=4) (actual time=0.140..0.140 rows=3 loops=1191)	
                              Buckets: 1024  Batches: 1  Memory Usage: 9kB	
                              ->  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=1191)	
                                    Recheck Cond: (path @> ARRAY[s3_1.id])	
                                    Filter: (id > 0)	
                                    Heap Blocks: exact=2410	
                                    ->  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=1191)	
                                          Index Cond: (path @> ARRAY[s3_1.id])	
          SubPlan 8	
            ->  Aggregate  (cost=18.48..18.49 rows=1 width=8) (actual time=0.031..0.031 rows=1 loops=1191)	
                  ->  Hash Join  (cost=17.25..18.47 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=1191)	
                        Hash Cond: (s1a_1.folder_id = tfi_2.id)	
                        ->  CTE Scan on s1a s1a_1  (cost=0.00..1.02 rows=51 width=4) (actual time=0.000..0.001 rows=6 loops=1191)	
                        ->  Hash  (cost=17.13..17.13 rows=10 width=4) (actual time=0.024..0.024 rows=3 loops=1191)	
                              Buckets: 1024  Batches: 1  Memory Usage: 9kB	
                              ->  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=1191)	
                                    Recheck Cond: (path @> ARRAY[s3_1.id])	
                                    Filter: (id > 0)	
                                    Heap Blocks: exact=2410	
                                    ->  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=1191)	
                                          Index Cond: (path @> ARRAY[s3_1.id])	
Planning time: 2.365 ms	
Execution time: 468.541 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	4.764 ms	1.0 %
Bitmap Heap Scan	3	16.674 ms	3.6 %
Bitmap Index Scan	3	273.930 ms	58.5 %
CTE Scan	8	623.327 ms	133.2 %
Hash	3	5.031 ms	1.1 %
Hash Join	3	149.970 ms	32.1 %
HashAggregate	1	0.008 ms	0.0 %
Index Scan	2	0.489 ms	0.1 %
Limit	1	0.002 ms	0.0 %
Nested Loop	4	0.046 ms	0.0 %
Recursive Union	1	1.047 ms	0.2 %
Seq Scan	4	4.070 ms	0.9 %
WorkTable Scan on s3	1	0.357 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
perspectives	1	0.018 ms	0.0 %
Seq Scan	1	0.018 ms	100.0 %
task	1	0.036 ms	0.0 %
Index Scan	1	0.036 ms	100.0 %
task_folders	5	20.704 ms	4.4 %
Bitmap Heap Scan	3	16.674 ms	80.5 %
Seq Scan	2	4.030 ms	19.5 %
task_user_tags	1	0.022 ms	0.0 %
Seq Scan	1	0.022 ms	100.0 %
users	1	0.453 ms	0.1 %
Index Scan	1	0.453 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4JR

Color mode:

Visible columns:

Per node type stats

Per table stats