explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rkes : cfms_get_post_role_login

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 427.317 ↓ 6.0 6 1

Unique (cost=3,429.56..3,429.57 rows=1 width=143) (actual time=427.306..427.317 rows=6 loops=1)

2. 0.099 427.301 ↓ 12.0 12 1

Sort (cost=3,429.56..3,429.56 rows=1 width=143) (actual time=427.299..427.301 rows=12 loops=1)

  • Sort Key: ((aa.post_id)::text), ((cc.role_id)::text), dd.role_name, (((((hh.office_name)::text || '('::text) || (ee.post_full_name)::text) || ')'::text))
  • Sort Method: quicksort Memory: 28kB
3. 0.124 427.202 ↓ 12.0 12 1

Nested Loop (cost=2.82..3,429.55 rows=1 width=143) (actual time=33.722..427.202 rows=12 loops=1)

4. 0.048 426.946 ↓ 12.0 12 1

Nested Loop (cost=2.53..3,429.21 rows=1 width=108) (actual time=33.671..426.946 rows=12 loops=1)

  • Join Filter: (aa.post_id = ee.post_id)
5. 0.044 426.754 ↓ 12.0 12 1

Nested Loop (cost=2.11..3,428.67 rows=1 width=87) (actual time=33.647..426.754 rows=12 loops=1)

  • Join Filter: (aa.post_id = gg.post_id)
6. 19.388 426.554 ↓ 12.0 12 1

Nested Loop (cost=1.69..3,428.16 rows=1 width=71) (actual time=33.619..426.554 rows=12 loops=1)

  • Join Filter: (bb.user_id = aa.emp_id)
  • Rows Removed by Join Filter: 28762
7. 10.860 66.570 ↓ 8.4 37,844 1

Nested Loop (cost=1.27..893.30 rows=4,500 width=71) (actual time=0.833..66.570 rows=37,844 loops=1)

  • Join Filter: (dd.role_id = ff.role_id)
8. 0.024 2.298 ↓ 2.0 6 1

Nested Loop (cost=0.85..738.40 rows=3 width=71) (actual time=0.811..2.298 rows=6 loops=1)

9. 0.016 2.220 ↓ 2.0 6 1

Nested Loop (cost=0.71..737.89 rows=3 width=30) (actual time=0.800..2.220 rows=6 loops=1)

10. 2.128 2.128 ↑ 1.0 1 1

Index Scan using org_user_mst_idx2 on org_user_mst bb (cost=0.29..720.25 rows=1 width=22) (actual time=0.757..2.128 rows=1 loops=1)

  • Index Cond: ((user_name)::text = 'pradeep.sah'::text)
  • Filter: (activate_flag = '1'::numeric)
11. 0.076 0.076 ↓ 2.0 6 1

Index Scan using cfms_acl_user_role_mpg_idx2 on cfms_acl_user_role_mpg cc (cost=0.42..17.62 rows=3 width=14) (actual time=0.032..0.076 rows=6 loops=1)

  • Index Cond: ((user_id = (bb.user_id)::numeric) AND (module_id = '5'::numeric))
  • Filter: (active_flag = 'Y'::bpchar)
  • Rows Removed by Filter: 3
12. 0.054 0.054 ↑ 1.0 1 6

Index Scan using fki_acl_role_details_rlt_fk1 on acl_role_details_rlt dd (cost=0.14..0.16 rows=1 width=41) (actual time=0.008..0.009 rows=1 loops=6)

  • Index Cond: (role_id = cc.role_id)
13. 53.412 53.412 ↓ 4.2 6,307 6

Index Scan using fki_cfms_acl_post_role_mpg_fk1 on cfms_acl_post_role_mpg ff (cost=0.42..32.72 rows=1,513 width=16) (actual time=0.020..8.902 rows=6,307 loops=6)

  • Index Cond: (role_id = cc.role_id)
  • Filter: (activate_flag = 'Y'::bpchar)
  • Rows Removed by Filter: 248
14. 340.596 340.596 ↑ 1.0 1 37,844

Index Scan using idx_org_emp_post_mpg_1 on org_emp_post_mpg aa (cost=0.42..0.55 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=37,844)

  • Index Cond: ((post_id = ff.post_id) AND (status = 1))
15. 0.156 0.156 ↑ 1.0 1 12

Index Scan using fki_org_post_office_mpg_fk1 on org_post_office_mpg gg (cost=0.42..0.50 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=12)

  • Index Cond: (post_id = ff.post_id)
16. 0.144 0.144 ↑ 1.0 1 12

Index Scan using fki_org_post_dtls_fk1 on org_post_dtls ee (cost=0.42..0.52 rows=1 width=45) (actual time=0.012..0.012 rows=1 loops=12)

  • Index Cond: (post_id = gg.post_id)
17. 0.132 0.132 ↑ 1.0 1 12

Index Scan using org_office_dtls_idx2 on org_office_dtls hh (cost=0.29..0.31 rows=1 width=41) (actual time=0.011..0.011 rows=1 loops=12)

  • Index Cond: (office_id = gg.office_id)
Planning time : 29.227 ms
Execution time : 427.607 ms