explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q3gA6 : Optimization for: Optimization for: Optimization for: plan #nmRA; plan #TpzW; plan #sIJn

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 4.052 ↑ 1.0 5 1

Limit (cost=4,362.11..4,795.97 rows=5 width=20) (actual time=3.887..4.052 rows=5 loops=1)

2.          

CTE assignedusergroupidcte

3. 0.002 0.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.58..12.69 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Join Filter: (user0_.usr_id = assignedus1_.uga_usr_id)
4. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using idx8_user on ""USER"" user0_ (cost=0.29..8.30 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (usr_id = 100,016,731)
  • Heap Fetches: 0
5. 0.005 0.005 ↑ 3.0 1 1

Index Only Scan using idx1_usr_grp_ass on usr_grp_ass assignedus1_ (cost=0.29..4.34 rows=3 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (uga_usr_id = 100,016,731)
  • Heap Fetches: 1
6.          

Initplan (for Limit)

7. 0.007 2.785 ↑ 1.0 1 1

Aggregate (cost=4,344.36..4,344.37 rows=1 width=8) (actual time=2.785..2.785 rows=1 loops=1)

8. 0.000 2.778 ↑ 1.0 50 1

Limit (cost=5.05..4,343.74 rows=50 width=4) (actual time=0.977..2.778 rows=50 loops=1)

9.          

Initplan (for Limit)

10. 0.001 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.47 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)

11. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus6_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

12. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using pk_user_group_permission on user_group_permission usergroupp5_ (cost=0.42..4.44 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((ugp_role_id = assignedus6_.id) AND (ugp_action_name = 'READ'::text) AND (ugp_entity = 'Document'::text))
  • Heap Fetches: 0
13. 0.016 2.774 ↑ 404.2 50 1

Nested Loop (cost=0.57..1,753,612.42 rows=20,209 width=4) (actual time=0.977..2.774 rows=50 loops=1)

14. 2.157 2.503 ↑ 397.7 51 1

Index Scan using idx_document_table on dm_document document2_ (cost=0.29..1,740,291.76 rows=20,283 width=4) (actual time=0.973..2.503 rows=51 loops=1)

  • Filter: ((enabled_flag OR ((NOT enabled_flag) AND (owner_user_id = 100,016,731))) AND (owner_company_category_entry_id = 5,651) AND ($2 OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 4,821
15.          

SubPlan (for Index Scan)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..54.64 rows=1 width=0) (never executed)

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on assignedusergroupidcte assignedus8_ (cost=0.00..0.02 rows=1 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd7_ (cost=0.42..54.61 rows=1 width=4) (never executed)

  • Index Cond: ((ugdp_role_id = assignedus8_.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Filter: ((ugdp_entityid)::integer = document2_.id)
  • Heap Fetches: 0
19. 0.105 0.346 ↓ 6.4 1,105 1

Nested Loop (cost=0.42..55.93 rows=173 width=4) (actual time=0.008..0.346 rows=1,105 loops=1)

20. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus8__1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

21. 0.241 0.241 ↓ 6.4 1,105 1

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd7__1 (cost=0.42..53.31 rows=173 width=13) (actual time=0.007..0.241 rows=1,105 loops=1)

  • Index Cond: ((ugdp_role_id = assignedus8__1.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Heap Fetches: 0
22. 0.255 0.255 ↑ 1.0 1 51

Index Scan using sql140911161336230 on dm_document_version documentve3_ (cost=0.29..0.66 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=51)

  • Index Cond: (id = document2_.document_version)
  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 0
23. 0.000 0.019 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.47 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1)

24. 0.014 0.014 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus11_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

25. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using pk_user_group_permission on user_group_permission usergroupp10_ (cost=0.42..4.44 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((ugp_role_id = assignedus11_.id) AND (ugp_action_name = 'READ'::text) AND (ugp_entity = 'Document'::text))
  • Heap Fetches: 0
26. 2.789 4.051 ↑ 4,041.8 5 1

Nested Loop (cost=0.57..1,753,612.42 rows=20,209 width=20) (actual time=3.886..4.051 rows=5 loops=1)

27. 0.898 1.247 ↑ 4,056.6 5 1

Index Scan using idx_document_table on dm_document document0_ (cost=0.29..1,740,291.76 rows=20,283 width=16) (actual time=1.091..1.247 rows=5 loops=1)

  • Filter: ((enabled_flag OR ((NOT enabled_flag) AND (owner_user_id = 100,016,731))) AND (owner_company_category_entry_id = 5,651) AND ($10 OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 1,893
28.          

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..54.64 rows=1 width=0) (never executed)

30. 0.000 0.000 ↓ 0.0 0

CTE Scan on assignedusergroupidcte assignedus13_ (cost=0.00..0.02 rows=1 width=4) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd12_ (cost=0.42..54.61 rows=1 width=4) (never executed)

  • Index Cond: ((ugdp_role_id = assignedus13_.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Filter: ((ugdp_entityid)::integer = document0_.id)
  • Heap Fetches: 0
32. 0.105 0.349 ↓ 6.4 1,105 1

Nested Loop (cost=0.42..55.93 rows=173 width=4) (actual time=0.009..0.349 rows=1,105 loops=1)

33. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus13__1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

34. 0.244 0.244 ↓ 6.4 1,105 1

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd12__1 (cost=0.42..53.31 rows=173 width=13) (actual time=0.008..0.244 rows=1,105 loops=1)

  • Index Cond: ((ugdp_role_id = assignedus13__1.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Heap Fetches: 0
35. 0.015 0.015 ↑ 1.0 1 5

Index Scan using sql140911161336230 on dm_document_version documentve1_ (cost=0.29..0.66 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: (id = document0_.document_version)
  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
Planning time : 0.949 ms
Execution time : 4.120 ms