explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nmRA

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 14,250.988 ↑ 1.0 5 1

Limit (cost=3,592,874.14..3,592,874.15 rows=5 width=20) (actual time=14,250.987..14,250.988 rows=5 loops=1)

  • Functions: 66
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.396 ms, Inlining 34.884 ms, Optimization 163.308 ms, Emission 126.179 ms, Total 327.767 ms
2.          

CTE assignedusergroupidcte

3. 0.003 0.079 ↑ 1.0 1 1

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

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

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

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

Initplan (for Limit)

7. 0.137 6,973.463 ↑ 1.0 1 1

Aggregate (cost=1,796,287.02..1,796,287.03 rows=1 width=8) (actual time=6,973.463..6,973.463 rows=1 loops=1)

8.          

Initplan (for Aggregate)

9. 0.002 0.007 ↓ 0.0 0 1

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

10. 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)

11. 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
12. 22.989 6,973.319 ↑ 19.5 1,044 1

Nested Loop (cost=0.29..1,796,231.62 rows=20,370 width=0) (actual time=4.605..6,973.319 rows=1,044 loops=1)

13. 12.017 12.017 ↓ 1.0 32,883 1

Seq Scan on dm_document_version documentve3_ (cost=0.00..1,966.01 rows=32,601 width=4) (actual time=0.005..12.017 rows=32,883 loops=1)

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 111
14. 65.298 6,938.313 ↓ 0.0 0 32,883

Index Scan using idx_dm_document_document_version on dm_document document2_ (cost=0.29..55.03 rows=1 width=4) (actual time=0.211..0.211 rows=0 loops=32,883)

  • Index Cond: (document_version = documentve3_.id)
  • Filter: ((NOT deleted_flag) AND (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: 1
15.          

SubPlan (for Index Scan)

16. 0.000 6,873.015 ↓ 0.0 0 26,953

Nested Loop (cost=0.42..54.64 rows=1 width=0) (actual time=0.255..0.255 rows=0 loops=26,953)

17. 0.000 0.000 ↑ 1.0 1 26,953

CTE Scan on assignedusergroupidcte assignedus8_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=26,953)

18. 6,873.015 6,873.015 ↓ 0.0 0 26,953

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd7_ (cost=0.42..54.61 rows=1 width=4) (actual time=0.255..0.255 rows=0 loops=26,953)

  • Index Cond: ((ugdp_role_id = assignedus8_.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Filter: ((ugdp_entityid)::integer = document2_.id)
  • Rows Removed by Filter: 1,084
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..55.93 rows=173 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

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

21. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: ((ugdp_role_id = assignedus8__1.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Heap Fetches: 0
22. 0.001 0.108 ↓ 0.0 0 1

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

23. 0.081 0.081 ↑ 1.0 1 1

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

24. 0.026 0.026 ↓ 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.026..0.026 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
25. 0.458 13,926.225 ↑ 4,074.0 5 1

Sort (cost=1,796,569.96..1,796,620.88 rows=20,370 width=20) (actual time=13,926.225..13,926.225 rows=5 loops=1)

  • Sort Key: documentve1_.creation_date DESC, document0_.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
26. 6,977.133 13,925.767 ↑ 19.5 1,044 1

Nested Loop (cost=0.29..1,796,231.62 rows=20,370 width=20) (actual time=6,985.137..13,925.767 rows=1,044 loops=1)

27. 10.321 10.321 ↓ 1.0 32,883 1

Seq Scan on dm_document_version documentve1_ (cost=0.00..1,966.01 rows=32,601 width=12) (actual time=0.019..10.321 rows=32,883 loops=1)

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 111
28. 65.298 6,938.313 ↓ 0.0 0 32,883

Index Scan using idx_dm_document_document_version on dm_document document0_ (cost=0.29..55.03 rows=1 width=8) (actual time=0.211..0.211 rows=0 loops=32,883)

  • Index Cond: (document_version = documentve1_.id)
  • Filter: ((NOT deleted_flag) AND (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
29.          

SubPlan (for Index Scan)

30. 26.953 6,873.015 ↓ 0.0 0 26,953

Nested Loop (cost=0.42..54.64 rows=1 width=0) (actual time=0.255..0.255 rows=0 loops=26,953)

31. 0.000 0.000 ↑ 1.0 1 26,953

CTE Scan on assignedusergroupidcte assignedus13_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=26,953)

32. 6,846.062 6,846.062 ↓ 0.0 0 26,953

Index Only Scan using pk_user_group_data_permission on user_group_data_permission usergroupd12_ (cost=0.42..54.61 rows=1 width=4) (actual time=0.254..0.254 rows=0 loops=26,953)

  • Index Cond: ((ugdp_role_id = assignedus13_.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Filter: ((ugdp_entityid)::integer = document0_.id)
  • Rows Removed by Filter: 1,084
  • Heap Fetches: 0
33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..55.93 rows=173 width=4) (never executed)

34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: ((ugdp_role_id = assignedus13__1.id) AND (ugdp_action_name = 'READ'::text) AND (ugdp_entity = 'Document'::text))
  • Heap Fetches: 0
Execution time : 14,254.532 ms