explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 170.088 7,363.801 ↑ 1.0 5 1

Limit (cost=1,801,001.52..1,801,001.53 rows=5 width=20) (actual time=7,363.800..7,363.801 rows=5 loops=1)

  • Functions: 67
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.273 ms, Inlining 7.700 ms, Optimization 181.419 ms, Emission 137.295 ms, Total 331.688 ms
2.          

CTE assignedusergroupidcte

3. 0.004 0.019 ↑ 1.0 1 1

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

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

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

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

Initplan (for Limit)

7. 0.015 156.762 ↑ 1.0 1 1

Aggregate (cost=4,414.40..4,414.41 rows=1 width=8) (actual time=156.761..156.762 rows=1 loops=1)

8. 0.000 156.747 ↑ 1.0 50 1

Limit (cost=4.76..4,413.77 rows=50 width=4) (actual time=5.442..156.747 rows=50 loops=1)

9.          

Initplan (for Limit)

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

11. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus6_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 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.000 156.741 ↑ 407.4 50 1

Nested Loop (cost=0.29..1,796,231.62 rows=20,370 width=4) (actual time=5.441..156.741 rows=50 loops=1)

14. 0.262 0.262 ↑ 40.6 803 1

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

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 2
15. 2.191 156.585 ↓ 0.0 0 803

Index Scan using idx_dm_document_document_version on dm_document document2_ (cost=0.29..55.03 rows=1 width=4) (actual time=0.194..0.195 rows=0 loops=803)

  • 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
16.          

SubPlan (for Index Scan)

17. 0.000 154.394 ↓ 0.0 0 646

Nested Loop (cost=0.42..54.64 rows=1 width=0) (actual time=0.239..0.239 rows=0 loops=646)

18. 0.000 0.000 ↑ 1.0 1 646

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

19. 154.394 154.394 ↓ 0.0 0 646

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.239..0.239 rows=0 loops=646)

  • 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,050
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

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

21. 0.000 0.000 ↓ 0.0 0

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

22. 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
23. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.47 rows=1 width=0) (actual time=0.028..0.028 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.006 0.006 ↓ 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.006..0.006 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. 0.435 7,036.923 ↑ 4,074.0 5 1

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

  • Sort Key: document0_.modification_date DESC, document0_.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
27. 153.301 7,036.488 ↑ 19.5 1,044 1

Nested Loop (cost=0.29..1,796,231.62 rows=20,370 width=20) (actual time=162.131..7,036.488 rows=1,044 loops=1)

28. 10.640 10.640 ↓ 1.0 32,883 1

Seq Scan on dm_document_version documentve1_ (cost=0.00..1,966.01 rows=32,601 width=4) (actual time=0.011..10.640 rows=32,883 loops=1)

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 111
29. 80.391 6,872.547 ↓ 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=16) (actual time=0.209..0.209 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
30.          

SubPlan (for Index Scan)

31. 26.953 6,792.156 ↓ 0.0 0 26,953

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

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

33. 6,765.203 6,765.203 ↓ 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.251..0.251 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
34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

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

36. 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 : 7,369.260 ms