explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DjbY

Settings
# 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