explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nMUA

Settings
# exclusive inclusive rows x rows loops node
1. 0.145 599.492 ↑ 2.3 32 1

Hash Left Join (cost=66.73..471,717.33 rows=75 width=392) (actual time=19.529..599.492 rows=32 loops=1)

  • Hash Cond: ((organization_t.st_cd)::text = (organization_t_2.st_cd)::text)
2. 0.038 0.520 ↓ 1.0 32 1

Nested Loop Left Join (cost=6.13..213.61 rows=31 width=293) (actual time=0.070..0.520 rows=32 loops=1)

3. 0.044 0.418 ↓ 1.0 32 1

Nested Loop Left Join (cost=5.86..198.95 rows=31 width=246) (actual time=0.061..0.418 rows=32 loops=1)

4. 0.035 0.374 ↓ 1.0 32 1

Nested Loop Left Join (cost=5.58..180.16 rows=31 width=170) (actual time=0.055..0.374 rows=32 loops=1)

5. 0.044 0.243 ↓ 1.0 32 1

Nested Loop (cost=5.29..169.61 rows=31 width=160) (actual time=0.045..0.243 rows=32 loops=1)

6. 0.021 0.103 ↓ 1.0 32 1

Nested Loop (cost=5.01..160.41 rows=31 width=92) (actual time=0.037..0.103 rows=32 loops=1)

7. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ixf02_pin on dcpsuser_t (cost=0.28..8.30 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: ((pin)::text = '903023'::text)
8. 0.056 0.069 ↑ 1.0 32 1

Bitmap Heap Scan on userorganization_t (cost=4.73..151.79 rows=32 width=92) (actual time=0.022..0.069 rows=32 loops=1)

  • Recheck Cond: (user_uid = dcpsuser_t.user_uid)
  • Filter: (eff_endt IS NULL)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=8
9. 0.013 0.013 ↑ 1.2 34 1

Bitmap Index Scan on ixf02_useruid (cost=0.00..4.72 rows=40 width=0) (actual time=0.013..0.013 rows=34 loops=1)

  • Index Cond: (user_uid = dcpsuser_t.user_uid)
10. 0.096 0.096 ↑ 1.0 1 32

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.30 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=32)

  • Index Cond: (org_uid = userorganization_t.org_uid)
11. 0.096 0.096 ↓ 0.0 0 32

Index Scan using userwkldtrld_t_user_org_uid_idx on userwkldtrld_t (cost=0.29..0.34 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=32)

  • Index Cond: (userorganization_t.user_org_uid = user_org_uid)
12. 0.000 0.000 ↓ 0.0 0 32

Index Scan using ixpk_dcpsuser_t on dcpsuser_t dcpsuser_t_1 (cost=0.28..0.61 rows=1 width=76) (actual time=0.000..0.000 rows=0 loops=32)

  • Index Cond: (organization_t.org_ownr_uid = user_uid)
13. 0.064 0.064 ↑ 1.0 1 32

Index Scan using ixpk_organization_t on organization_t organization_t_1 (cost=0.28..0.47 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=32)

  • Index Cond: (organization_t.site_org_uid = org_uid)
14. 0.015 0.171 ↑ 1.0 56 1

Hash (cost=59.90..59.90 rows=56 width=31) (actual time=0.171..0.171 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.156 0.156 ↑ 1.0 56 1

Seq Scan on organization_t organization_t_2 (cost=0.00..59.90 rows=56 width=31) (actual time=0.007..0.156 rows=56 loops=1)

  • Filter: ((org_typ_cd)::text = 'T'::text)
  • Rows Removed by Filter: 1134
16.          

SubPlan (forHash Left Join)

17. 0.384 598.048 ↑ 1.0 1 32

Aggregate (cost=6,276.66..6,276.67 rows=1 width=32) (actual time=18.689..18.689 rows=1 loops=32)

18. 2.976 597.664 ↓ 4.0 76 32

Sort (cost=6,276.37..6,276.42 rows=19 width=20) (actual time=18.673..18.677 rows=76 loops=32)

  • Sort Key: independentpermission_t.prmis_cd
  • Sort Method: quicksort Memory: 28kB
19. 0.960 594.688 ↓ 4.0 76 32

HashAggregate (cost=6,275.78..6,275.97 rows=19 width=20) (actual time=18.576..18.584 rows=76 loops=32)

  • Group Key: independentpermission_t.prmis_cd
20. 0.224 593.728 ↓ 4.5 85 32

Append (cost=0.00..6,275.73 rows=19 width=20) (actual time=10.190..18.554 rows=85 loops=32)

21. 0.336 29.696 ↓ 2.0 2 32

Nested Loop (cost=0.00..409.51 rows=1 width=5) (actual time=0.877..0.928 rows=2 loops=32)

  • Join Filter: ((independentpermission_t.prmis_cd)::text = (permission_t.prmis_cd)::text)
  • Rows Removed by Join Filter: 98
22. 28.800 28.800 ↓ 2.0 2 32

Seq Scan on independentpermission_t (cost=0.00..404.23 rows=1 width=5) (actual time=0.874..0.900 rows=2 loops=32)

  • Filter: (user_org_uid = userorganization_t.user_org_uid)
  • Rows Removed by Filter: 14736
23. 0.560 0.560 ↑ 1.8 40 80

Seq Scan on permission_t (cost=0.00..4.40 rows=71 width=5) (actual time=0.001..0.007 rows=40 loops=80)

  • Filter: ((eff_endt IS NULL) OR (eff_endt > '2019-05-13'::date))
  • Rows Removed by Filter: 34
24. 0.535 563.808 ↓ 4.6 82 32

Nested Loop (cost=36.62..5,866.03 rows=18 width=5) (actual time=10.058..17.619 rows=82 loops=32)

  • Join Filter: (organizationrole_t.role_uid = dcpsrole_t.role_uid)
25. 1.164 560.640 ↓ 4.6 82 32

Nested Loop (cost=36.34..5,860.27 rows=18 width=13) (actual time=10.053..17.520 rows=82 loops=32)

  • Join Filter: (organizationrole_t.org_role_uid = userorganizationrole_t.org_role_uid)
  • Rows Removed by Join Filter: 406
26. 484.608 484.608 ↑ 1.0 1 32

Seq Scan on userorganizationrole_t (cost=0.00..5,291.93 rows=1 width=4) (actual time=9.266..15.144 rows=1 loops=32)

  • Filter: ((eff_strt_ts <= '2019-05-13 12:50:11.851-04'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-05-13 12:50:11.851-04'::timestamp with time zone)) AND (user_org_uid = userorganization_t.user_org_uid))
  • Rows Removed by Filter: 189856
27. 39.076 74.868 ↑ 1.4 459 34

Hash Join (cost=36.34..560.28 rows=645 width=17) (actual time=0.622..2.202 rows=459 loops=34)

  • Hash Cond: (rolepermission_t.role_uid = organizationrole_t.role_uid)
28. 33.456 33.456 ↓ 1.0 14,804 34

Seq Scan on rolepermission_t (cost=0.00..461.99 rows=14,799 width=9) (actual time=0.001..0.984 rows=14,804 loops=34)

29. 0.160 2.336 ↑ 1.4 26 32

Hash (cost=35.89..35.89 rows=36 width=8) (actual time=0.073..0.073 rows=26 loops=32)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 2.176 2.176 ↑ 1.4 26 32

Seq Scan on organizationrole_t (cost=0.00..35.89 rows=36 width=8) (actual time=0.020..0.068 rows=26 loops=32)

  • Filter: ((org_uid = organization_t_2.org_uid) OR (org_uid = organization_t.site_org_uid))
  • Rows Removed by Filter: 768
31. 2.633 2.633 ↑ 1.0 1 2,633

Index Scan using ixpk_dcpsrole_t on dcpsrole_t (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,633)

  • Index Cond: (role_uid = rolepermission_t.role_uid)
  • Filter: ((eff_strt_ts <= '2019-05-13 12:50:11.851-04'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-05-13 12:50:11.851-04'::timestamp with time zone)))
32. 0.096 0.608 ↑ 1.0 1 32

Aggregate (cost=9.19..9.20 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=32)

33. 0.256 0.512 ↑ 1.1 8 32

Sort (cost=9.05..9.07 rows=9 width=11) (actual time=0.015..0.016 rows=8 loops=32)

  • Sort Key: userdshb_t.dflt_dshb_sw DESC, userdshb_t.eff_stdt
  • Sort Method: quicksort Memory: 25kB
34. 0.256 0.256 ↑ 1.1 8 32

Index Scan using ixf01_userdshb_t on userdshb_t (cost=0.43..8.91 rows=9 width=11) (actual time=0.006..0.008 rows=8 loops=32)

  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_stdt <= '2019-05-13'::date) AND ((eff_endt IS NULL) OR (eff_endt > '2019-05-13'::date)))