explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TpzW : Optimization for: plan #nmRA

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 115.818 7,499.643 ↑ 1.0 5 1

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

  • Functions: 67
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 4.575 ms, Inlining 6.564 ms, Optimization 151.115 ms, Emission 118.527 ms, Total 280.781 ms
2.          

CTE assignedusergroupidcte

3. 0.003 0.014 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.58..12.69 rows=1 width=4) (actual time=0.013..0.014 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.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

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

Initplan (for Limit)

7. 0.015 160.867 ↑ 1.0 1 1

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

8. 0.000 160.852 ↑ 1.0 50 1

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

9.          

Initplan (for Limit)

10. 0.002 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.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 160.846 ↑ 407.4 50 1

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

14. 0.249 0.249 ↑ 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.004..0.249 rows=803 loops=1)

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 2
15. 2.330 160.600 ↓ 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.200..0.200 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 158.270 ↓ 0.0 0 646

Nested Loop (cost=0.42..54.64 rows=1 width=0) (actual time=0.245..0.245 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. 158.270 158.270 ↓ 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.245..0.245 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.022 ↓ 0.0 0 1

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

24. 0.016 0.016 ↑ 1.0 1 1

CTE Scan on assignedusergroupidcte assignedus11_ (cost=0.00..0.02 rows=1 width=4) (actual time=0.015..0.016 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. 0.444 7,222.936 ↑ 4,074.0 5 1

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

  • Sort Key: documentve1_.creation_date DESC, document0_.id DESC
  • Sort Method: top-N heapsort Memory: 25kB
27. 174.504 7,222.492 ↑ 19.5 1,044 1

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

28. 11.026 11.026 ↓ 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.010..11.026 rows=32,883 loops=1)

  • Filter: ((archivation_status)::text = 'AVAILABLE'::text)
  • Rows Removed by Filter: 111
29. 56.135 7,036.962 ↓ 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.214..0.214 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,980.827 ↓ 0.0 0 26,953

Nested Loop (cost=0.42..54.64 rows=1 width=0) (actual time=0.259..0.259 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,953.874 6,953.874 ↓ 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.258..0.258 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,504.370 ms