explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yM1p

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 25,454.020 ↑ 27,686.9 70 1

Hash Left Join (cost=4,793,724.33..8,707,413.13 rows=1,938,083 width=1,013) (actual time=25,453.906..25,454.020 rows=70 loops=1)

  • Hash Cond: (apv.prop_id = api.id)
  • Join Filter: ((apv.resource_id = p1.id) AND ((p1.is_actor = 1) OR ((p1.access_admin = 1) AND (api.access_admin = 'y'::bpchar)) OR ((p1.access_owner = 1) AND (api.access_owner = 'y'::bpchar)) OR ((p1.
2.          

CTE p1

3. 0.002 25,432.711 ↑ 32,767.5 2 1

Limit (cost=4,790,053.63..4,791,692.01 rows=65,535 width=162) (actual time=25,432.702..25,432.711 rows=2 loops=1)

4. 0.009 25,432.709 ↑ 50,676.5 2 1

Unique (cost=4,790,053.63..4,792,587.46 rows=101,353 width=162) (actual time=25,432.700..25,432.709 rows=2 loops=1)

5. 0.035 25,432.700 ↑ 50,676.5 2 1

Sort (cost=4,790,053.63..4,790,307.02 rows=101,353 width=162) (actual time=25,432.698..25,432.700 rows=2 loops=1)

  • Sort Key: ar1.uid, ar1.id, ar1.app_id, ar1.resource_type, ar1.revision, ar1.last_mod_time, ar1.status, ar1.service_id, ar1.subscription
  • Sort Method: quicksort Memory: 25kB
6. 7,430.921 25,432.665 ↑ 50,676.5 2 1

Bitmap Heap Scan on aps_resource ar1 (cost=12,822.07..4,781,626.62 rows=101,353 width=162) (actual time=8,431.482..25,432.665 rows=2 loops=1)

  • Recheck Cond: (type_info_id = 871)
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 203,302
  • Heap Blocks: exact=132,621
7. 314.296 314.296 ↓ 1.0 203,760 1

Bitmap Index Scan on "IX_aps_resource_aps_type_info" (cost=0.00..12,796.73 rows=202,707 width=0) (actual time=314.296..314.296 rows=203,760 loops=1)

  • Index Cond: (type_info_id = 871)
8.          

SubPlan (for Bitmap Heap Scan)

9. 203.388 17,687.448 ↓ 0.0 0 203,304

Append (cost=9.02..34.11 rows=2 width=4) (actual time=0.087..0.087 rows=0 loops=203,304)

10. 406.592 406.608 ↓ 0.0 0 203,304

Index Scan using "IX_aps_resource_list_edge" on aps_resource_link arllw (cost=9.02..17.04 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=203,304)

  • Index Cond: ((source_resource_id = $0) AND (target_resource_id = ar1.id))
11.          

Initplan (for Index Scan)

12. 0.016 0.016 ↑ 1.0 1 1

Index Scan using "IX_aps_resource_uid" on aps_resource arlw (cost=0.43..8.45 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)

  • Index Cond: (uid = 'd850d7b5-8e7d-4247-b219-84d19a7749cd'::uuid)
13. 17,077.448 17,077.452 ↓ 0.0 0 203,303

Index Scan using "IX_aps_resource_list_edge" on aps_resource_link arllw_1 (cost=9.02..17.04 rows=1 width=4) (actual time=0.084..0.084 rows=0 loops=203,303)

  • Index Cond: ((source_resource_id = ar1.id) AND (target_resource_id = $2))
14.          

Initplan (for Index Scan)

15. 0.004 0.004 ↑ 1.0 1 1

Index Scan using "IX_aps_resource_uid" on aps_resource arlw_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (uid = 'd850d7b5-8e7d-4247-b219-84d19a7749cd'::uuid)
16. 0.026 25,432.815 ↑ 27,686.9 70 1

Nested Loop Left Join (cost=0.56..3,908,601.89 rows=1,938,083 width=1,012) (actual time=25,432.726..25,432.815 rows=70 loops=1)

17. 25,432.715 25,432.715 ↑ 32,767.5 2 1

CTE Scan on p1 (cost=0.00..1,310.70 rows=65,535 width=866) (actual time=25,432.704..25,432.715 rows=2 loops=1)

18. 0.074 0.074 ↓ 1.2 35 2

Index Scan using "IX_aps_property_value_resource_id_prop_id_order_num" on aps_property_value apv (cost=0.56..59.32 rows=30 width=146) (actual time=0.013..0.037 rows=35 loops=2)

  • Index Cond: (p1.id = resource_id)
19. 10.070 21.102 ↑ 1.0 35,294 1

Hash (cost=1,589.67..1,589.67 rows=35,367 width=47) (actual time=21.102..21.102 rows=35,294 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,311kB
20. 11.032 11.032 ↑ 1.0 35,294 1

Seq Scan on aps_property_info api (cost=0.00..1,589.67 rows=35,367 width=47) (actual time=0.013..11.032 rows=35,294 loops=1)

Planning time : 1.750 ms
Execution time : 25,454.188 ms