explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

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

Limit (cost=4,309.77..4,738.40 rows=5 width=20) (actual time=6.251..6.563 rows=5 loops=1)

2.          

CTE assignedusergroupidcte

3. 0.002 0.020 ↑ 1.0 1 1

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

  • Join Filter: (user0_.usr_id = assignedus1_.uga_usr_id)
4. 0.005 0.005 ↑ 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.005..0.005 rows=1 loops=1)

  • Index Cond: (usr_id = 100,016,731)
  • Heap Fetches: 0
5. 0.013 0.013 ↑ 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.013..0.013 rows=1 loops=1)

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

Initplan (for Limit)

7. 0.008 4.166 ↑ 1.0 1 1

Aggregate (cost=4,292.02..4,292.03 rows=1 width=8) (actual time=4.165..4.166 rows=1 loops=1)

8. 0.000 4.158 ↑ 1.0 50 1

Limit (cost=5.05..4,291.40 rows=50 width=4) (actual time=1.829..4.158 rows=50 loops=1)

9.          

Initplan (for Limit)

10. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.47 rows=1 width=0) (actual time=0.005..0.005 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.004 0.004 ↓ 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.004..0.004 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.039 4.154 ↑ 405.4 50 1

Nested Loop (cost=0.57..1,737,601.32 rows=20,269 width=4) (actual time=1.829..4.154 rows=50 loops=1)

14. 3.254 3.962 ↑ 398.9 51 1

Index Scan using idx_document_table on dm_document document2_ (cost=0.29..1,724,261.91 rows=20,344 width=4) (actual time=1.823..3.962 rows=51 loops=1)

  • Index Cond: (owner_company_category_entry_id = 5,651)
  • Filter: ((enabled_flag OR ((NOT enabled_flag) AND (owner_user_id = 100,016,731))) AND ($2 OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 4,546
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.205 0.708 ↓ 6.4 1,105 1

Nested Loop (cost=0.42..55.93 rows=173 width=4) (actual time=0.011..0.708 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.503 0.503 ↓ 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.010..0.503 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.153 0.153 ↑ 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.003..0.003 rows=1 loops=51)

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

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

24. 0.021 0.021 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus11_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.020..0.021 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. 4.170 6.562 ↑ 4,053.8 5 1

Nested Loop (cost=0.57..1,737,601.32 rows=20,269 width=20) (actual time=6.250..6.562 rows=5 loops=1)

27. 1.607 2.377 ↑ 4,068.8 5 1

Index Scan using idx_document_table on dm_document document0_ (cost=0.29..1,724,261.91 rows=20,344 width=16) (actual time=2.076..2.377 rows=5 loops=1)

  • Index Cond: (owner_company_category_entry_id = 5,651)
  • Filter: ((enabled_flag OR ((NOT enabled_flag) AND (owner_user_id = 100,016,731))) AND ($10 OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 1,832
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.235 0.770 ↓ 6.4 1,105 1

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

33. 0.001 0.001 ↑ 1.0 1 1

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

34. 0.534 0.534 ↓ 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.011..0.534 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 : 1.361 ms
Execution time : 6.676 ms