#	exclusive	inclusive	rows x	rows	loops	node
1.	0.089	593,020.778	↑ 1.0	1	1	→ Aggregate (cost=4,038.44..4,038.45 rows=1 width=8) (actual time=593,020.778..593,020.778 rows=1 loops=1)
2.	0.008	593,020.689	↑ 1.0	1	1	→ Unique (cost=4,038.42..4,038.43 rows=1 width=4) (actual time=593,020.687..593,020.689 rows=1 loops=1)
3.	0.212	593,020.681	↑ 1.0	1	1	→ Sort (cost=4,038.42..4,038.43 rows=1 width=4) (actual time=593,020.680..593,020.681 rows=1 loops=1) Sort Key: ar1.id Sort Method: quicksort Memory: 25kB
4.	198.161	593,020.469	↑ 1.0	1	1	→ Nested Loop (cost=1,882.52..4,038.41 rows=1 width=4) (actual time=260,016.428..593,020.469 rows=1 loops=1)
5.	105.161	591,921.632	↓ 23,702.0	23,702	1	→ Nested Loop (cost=1,881.96..4,018.37 rows=1 width=8) (actual time=252.238..591,921.632 rows=23,702 loops=1)
6.	70,527.575	591,176.517	↓ 23,702.0	23,702	1	→ Nested Loop (cost=1,881.39..4,016.09 rows=1 width=12) (actual time=252.172..591,176.517 rows=23,702 loops=1) Join Filter: (ar1.id = "SELECT 1".trg_id) Rows Removed by Join Filter: 613384058
7.	84.649	564.462	↓ 25,880.0	25,880	1	→ Nested Loop (cost=1,815.46..1,844.77 rows=1 width=8) (actual time=38.064..564.462 rows=25,880 loops=1)
8.	19.556	117.493	↓ 25,880.0	25,880	1	→ Nested Loop (cost=1,815.03..1,843.13 rows=1 width=4) (actual time=37.911..117.493 rows=25,880 loops=1)
9.	0.137	0.137	↑ 1.0	1	1	→ Index Scan using "IX_aps_resource_uid" on aps_resource "ARCNDapplication" (cost=0.43..8.45 rows=1 width=4) (actual time=0.098..0.137 rows=1 loops=1) Index Cond: (uid = '9f1990fb-3205-4b60-b985-a41c42c4fc46'::uuid)
10.	60.567	97.800	↓ 5,176.0	25,880	1	→ Bitmap Heap Scan on aps_resource_link "ARCNDapplication_arl" (cost=1,814.60..1,834.64 rows=5 width=8) (actual time=37.797..97.800 rows=25,880 loops=1) Recheck Cond: ((target_resource_id = "ARCNDapplication".id) AND ((source_relation_id)::text = 'application'::text)) Heap Blocks: exact=1986
11.	0.877	37.233	↓ 0.0	0	1	→ BitmapAnd (cost=1,814.60..1,814.60 rows=5 width=0) (actual time=37.233..37.233 rows=0 loops=1)
12.	16.268	16.268	↓ 26.8	78,721	1	→ Bitmap Index Scan on "IX_aps_resource_link_target_resource_role_mask" (cost=0.00..86.61 rows=2,940 width=0) (actual time=16.268..16.268 rows=78,721 loops=1) Index Cond: (target_resource_id = "ARCNDapplication".id)
13.	20.088	20.088	↑ 1.4	41,407	1	→ Bitmap Index Scan on "IX_aps_resource_link_source_relation_id_source_resource_id" (cost=0.00..1,713.57 rows=56,667 width=0) (actual time=20.088..20.088 rows=41,407 loops=1) Index Cond: ((source_relation_id)::text = 'application'::text)
14.	362.320	362.320	↑ 1.0	1	25,880	→ Index Only Scan using "PK_aps_resource" on aps_resource ar1 (cost=0.43..1.63 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=25,880) Index Cond: (id = "ARCNDapplication_arl".source_resource_id) Heap Fetches: 2900
15.	66,071.640	520,084.480	↓ 41.6	23,702	25,880	→ Append (cost=65.94..2,164.19 rows=570 width=4) (actual time=0.031..20.096 rows=23,702 loops=25,880)
16.	0.000	207.040	↓ 0.0	0	25,880	→ Subquery Scan on SELECT 1 (cost=65.94..66.34 rows=16 width=4) (actual time=0.008..0.008 rows=0 loops=25,880)
17.	25.880	207.040	↓ 0.0	0	25,880	→ GroupAggregate (cost=65.94..66.18 rows=16 width=28) (actual time=0.008..0.008 rows=0 loops=25,880) Group Key: arl.target_resource_id
18.	21.385	181.160	↓ 0.0	0	25,880	→ Sort (cost=65.94..65.98 rows=16 width=4) (actual time=0.007..0.007 rows=0 loops=25,880) Sort Key: arl.target_resource_id Sort Method: quicksort Memory: 25kB
19.	159.775	159.775	↓ 0.0	0	1	→ Index Scan using "IX_aps_resource_link_target_type_info_id_source_app_id" on aps_resource_link arl (cost=0.56..65.62 rows=16 width=4) (actual time=159.775..159.775 rows=0 loops=1) Index Cond: ((target_type_info_id = 1175) AND (source_app_id = 6936)) Filter: ((target_app_id <> 6936) AND (role_mask = ANY ('{1,2,4}'::integer[]))) Rows Removed by Filter: 73598
20.	106,936.160	453,805.800	↓ 42.8	23,702	25,880	→ Subquery Scan on SELECT 2 (cost=0.43..2,097.85 rows=554 width=4) (actual time=0.023..17.535 rows=23,702 loops=25,880)
21.	346,869.640	346,869.640	↓ 42.8	23,702	25,880	→ Index Scan using "IX_aps_resource_type_info_id_app_id" on aps_resource ar2 (cost=0.43..2,092.31 rows=554 width=28) (actual time=0.023..13.403 rows=23,702 loops=25,880) Index Cond: ((type_info_id = 1175) AND (app_id = 6936))
22.	639.954	639.954	↑ 1.0	1	23,702	→ Index Scan using "IX_aps_resource_link_source_relation_id_source_resource_id" on aps_resource_link "APVCNDcore_account.idLNK" (cost=0.56..2.27 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=23,702) Index Cond: (((source_relation_id)::text = 'core_account'::text) AND (source_resource_id = ar1.id))
23.	900.676	900.676	↓ 0.0	0	23,702	→ Index Scan using "IX_aps_property_value_resource_id_prop_id_order_num" on aps_property_value "APVCNDcore_account.id" (cost=0.56..20.03 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=23,702) Index Cond: ((resource_id = "APVCNDcore_account.idLNK".target_resource_id) AND (prop_id = ANY ('{6657,3399,187,4667}'::integer[]))) Filter: (vbigint = 1000004810) Rows Removed by Filter: 1

                                                                                                                    QUERY PLAN                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4038.44..4038.45 rows=1 width=8) (actual time=593020.778..593020.778 rows=1 loops=1)
   ->  Unique  (cost=4038.42..4038.43 rows=1 width=4) (actual time=593020.687..593020.689 rows=1 loops=1)
         ->  Sort  (cost=4038.42..4038.43 rows=1 width=4) (actual time=593020.680..593020.681 rows=1 loops=1)
               Sort Key: ar1.id
               Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=1882.52..4038.41 rows=1 width=4) (actual time=260016.428..593020.469 rows=1 loops=1)
                     ->  Nested Loop  (cost=1881.96..4018.37 rows=1 width=8) (actual time=252.238..591921.632 rows=23702 loops=1)
                           ->  Nested Loop  (cost=1881.39..4016.09 rows=1 width=12) (actual time=252.172..591176.517 rows=23702 loops=1)
                                 Join Filter: (ar1.id = "*SELECT* 1".trg_id)
                                 Rows Removed by Join Filter: 613384058
                                 ->  Nested Loop  (cost=1815.46..1844.77 rows=1 width=8) (actual time=38.064..564.462 rows=25880 loops=1)
                                       ->  Nested Loop  (cost=1815.03..1843.13 rows=1 width=4) (actual time=37.911..117.493 rows=25880 loops=1)
                                             ->  Index Scan using "IX_aps_resource_uid" on aps_resource "ARCNDapplication"  (cost=0.43..8.45 rows=1 width=4) (actual time=0.098..0.137 rows=1 loops=1)
                                                   Index Cond: (uid = '9f1990fb-3205-4b60-b985-a41c42c4fc46'::uuid)
                                             ->  Bitmap Heap Scan on aps_resource_link "ARCNDapplication_arl"  (cost=1814.60..1834.64 rows=5 width=8) (actual time=37.797..97.800 rows=25880 loops=1)
                                                   Recheck Cond: ((target_resource_id = "ARCNDapplication".id) AND ((source_relation_id)::text = 'application'::text))
                                                   Heap Blocks: exact=1986
                                                   ->  BitmapAnd  (cost=1814.60..1814.60 rows=5 width=0) (actual time=37.233..37.233 rows=0 loops=1)
                                                         ->  Bitmap Index Scan on "IX_aps_resource_link_target_resource_role_mask"  (cost=0.00..86.61 rows=2940 width=0) (actual time=16.268..16.268 rows=78721 loops=1)
                                                               Index Cond: (target_resource_id = "ARCNDapplication".id)
                                                         ->  Bitmap Index Scan on "IX_aps_resource_link_source_relation_id_source_resource_id"  (cost=0.00..1713.57 rows=56667 width=0) (actual time=20.088..20.088 rows=41407 loops=1)
                                                               Index Cond: ((source_relation_id)::text = 'application'::text)
                                       ->  Index Only Scan using "PK_aps_resource" on aps_resource ar1  (cost=0.43..1.63 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=25880)
                                             Index Cond: (id = "ARCNDapplication_arl".source_resource_id)
                                             Heap Fetches: 2900
                                 ->  Append  (cost=65.94..2164.19 rows=570 width=4) (actual time=0.031..20.096 rows=23702 loops=25880)
                                       ->  Subquery Scan on "*SELECT* 1"  (cost=65.94..66.34 rows=16 width=4) (actual time=0.008..0.008 rows=0 loops=25880)
                                             ->  GroupAggregate  (cost=65.94..66.18 rows=16 width=28) (actual time=0.008..0.008 rows=0 loops=25880)
                                                   Group Key: arl.target_resource_id
                                                   ->  Sort  (cost=65.94..65.98 rows=16 width=4) (actual time=0.007..0.007 rows=0 loops=25880)
                                                         Sort Key: arl.target_resource_id
                                                         Sort Method: quicksort  Memory: 25kB
                                                         ->  Index Scan using "IX_aps_resource_link_target_type_info_id_source_app_id" on aps_resource_link arl  (cost=0.56..65.62 rows=16 width=4) (actual time=159.775..159.775 rows=0 loops=1)
                                                               Index Cond: ((target_type_info_id = 1175) AND (source_app_id = 6936))
                                                               Filter: ((target_app_id <> 6936) AND (role_mask = ANY ('{1,2,4}'::integer[])))
                                                               Rows Removed by Filter: 73598
                                       ->  Subquery Scan on "*SELECT* 2"  (cost=0.43..2097.85 rows=554 width=4) (actual time=0.023..17.535 rows=23702 loops=25880)
                                             ->  Index Scan using "IX_aps_resource_type_info_id_app_id" on aps_resource ar2  (cost=0.43..2092.31 rows=554 width=28) (actual time=0.023..13.403 rows=23702 loops=25880)
                                                   Index Cond: ((type_info_id = 1175) AND (app_id = 6936))
                           ->  Index Scan using "IX_aps_resource_link_source_relation_id_source_resource_id" on aps_resource_link "APVCNDcore_account.idLNK"  (cost=0.56..2.27 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=23702)
                                 Index Cond: (((source_relation_id)::text = 'core_account'::text) AND (source_resource_id = ar1.id))
                     ->  Index Scan using "IX_aps_property_value_resource_id_prop_id_order_num" on aps_property_value "APVCNDcore_account.id"  (cost=0.56..20.03 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=23702)
                           Index Cond: ((resource_id = "APVCNDcore_account.idLNK".target_resource_id) AND (prop_id = ANY ('{6657,3399,187,4667}'::integer[])))
                           Filter: (vbigint = 1000004810)
                           Rows Removed by Filter: 1
 Planning time: 12.348 ms
 Execution time: 593021.520 ms
(47 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.089 ms	0.0 %
Append	1	66,071.640 ms	11.1 %
Bitmap Heap Scan	1	60.567 ms	0.0 %
Bitmap Index Scan	2	36.356 ms	0.0 %
BitmapAnd	1	0.877 ms	0.0 %
GroupAggregate	1	25.880 ms	0.0 %
Index Only Scan	1	362.320 ms	0.1 %
Index Scan	5	348,570.182 ms	58.8 %
Nested Loop	5	70,935.102 ms	12.0 %
Sort	2	21.597 ms	0.0 %
Subquery Scan	2	106,936.160 ms	18.0 %
Unique	1	0.008 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
aps_property_value	1	900.676 ms	0.2 %
Index Scan	1	900.676 ms	100.0 %
aps_resource	3	347,232.097 ms	58.6 %
Index Only Scan	1	362.320 ms	0.1 %
Index Scan	2	346,869.777 ms	99.9 %
aps_resource_link	3	860.296 ms	0.1 %
Bitmap Heap Scan	1	60.567 ms	7.0 %
Index Scan	2	799.729 ms	93.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DjbY

Color mode:

Visible columns:

Per node type stats

Per table stats