explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UwH

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 4.354 ↑ 68.0 1 1

Hash Left Join (cost=68.27..68,594.39 rows=68 width=320) (actual time=4.352..4.354 rows=1 loops=1)

  • Hash Cond: ((organization_t.st_cd)::text = (organization_t_2.st_cd)::text)
2. 0.001 0.048 ↑ 31.0 1 1

Nested Loop Left Join (cost=5.85..194.31 rows=31 width=221) (actual time=0.046..0.048 rows=1 loops=1)

3. 0.002 0.041 ↑ 31.0 1 1

Nested Loop Left Join (cost=5.58..180.16 rows=31 width=174) (actual time=0.039..0.041 rows=1 loops=1)

4. 0.001 0.033 ↑ 31.0 1 1

Nested Loop (cost=5.29..169.61 rows=31 width=164) (actual time=0.032..0.033 rows=1 loops=1)

5. 0.002 0.026 ↑ 31.0 1 1

Nested Loop (cost=5.01..160.41 rows=31 width=92) (actual time=0.025..0.026 rows=1 loops=1)

6. 0.016 0.016 ↑ 1.0 1 1

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

  • Index Cond: ((pin)::text = '904289'::text)
7. 0.004 0.008 ↑ 32.0 1 1

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

  • Recheck Cond: (user_uid = dcpsuser_t.user_uid)
  • Filter: (eff_endt IS NULL)
  • Heap Blocks: exact=1
8. 0.004 0.004 ↑ 40.0 1 1

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

  • Index Cond: (user_uid = dcpsuser_t.user_uid)
9. 0.006 0.006 ↑ 1.0 1 1

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.30 rows=1 width=76) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (org_uid = userorganization_t.org_uid)
10. 0.006 0.006 ↓ 0.0 0 1

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

  • Index Cond: (userorganization_t.user_org_uid = user_org_uid)
11. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (organization_t.site_org_uid = org_uid)
12. 0.017 0.202 ↑ 1.0 56 1

Hash (cost=61.71..61.71 rows=56 width=31) (actual time=0.202..0.202 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.185 0.185 ↑ 1.0 56 1

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

  • Filter: ((org_typ_cd)::text = 'T'::text)
  • Rows Removed by Filter: 1287
14.          

SubPlan (forHash Left Join)

15. 0.022 4.045 ↑ 1.0 1 1

Aggregate (cost=990.87..990.88 rows=1 width=32) (actual time=4.045..4.045 rows=1 loops=1)

16. 0.151 4.023 ↓ 5.7 108 1

Sort (cost=990.58..990.63 rows=19 width=20) (actual time=4.018..4.023 rows=108 loops=1)

  • Sort Key: independentpermission_t.prmis_cd
  • Sort Method: quicksort Memory: 30kB
17. 0.050 3.872 ↓ 5.7 108 1

HashAggregate (cost=989.98..990.17 rows=19 width=20) (actual time=3.861..3.872 rows=108 loops=1)

  • Group Key: independentpermission_t.prmis_cd
18. 0.010 3.822 ↓ 5.8 111 1

Append (cost=0.00..989.94 rows=19 width=20) (actual time=1.121..3.822 rows=111 loops=1)

19. 0.000 1.067 ↓ 0.0 0 1

Nested Loop (cost=0.00..412.69 rows=1 width=5) (actual time=1.067..1.067 rows=0 loops=1)

  • Join Filter: ((independentpermission_t.prmis_cd)::text = (permission_t.prmis_cd)::text)
20. 1.067 1.067 ↓ 0.0 0 1

Seq Scan on independentpermission_t (cost=0.00..405.65 rows=1 width=5) (actual time=1.067..1.067 rows=0 loops=1)

  • Filter: (user_org_uid = userorganization_t.user_org_uid)
  • Rows Removed by Filter: 14782
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on permission_t (cost=0.00..5.86 rows=94 width=5) (never executed)

  • Filter: ((eff_endt IS NULL) OR (eff_endt > '2019-07-09'::date))
22. 0.013 2.745 ↓ 6.2 111 1

Nested Loop (cost=19.46..577.06 rows=18 width=5) (actual time=0.052..2.745 rows=111 loops=1)

  • Join Filter: (organizationrole_t.role_uid = dcpsrole_t.role_uid)
23. 1.238 2.621 ↓ 6.2 111 1

Hash Join (cost=19.19..571.33 rows=18 width=13) (actual time=0.043..2.621 rows=111 loops=1)

  • Hash Cond: (rolepermission_t.role_uid = organizationrole_t.role_uid)
24. 1.361 1.361 ↓ 1.0 17,307 1

Seq Scan on rolepermission_t (cost=0.00..487.06 rows=17,306 width=9) (actual time=0.005..1.361 rows=17,307 loops=1)

25. 0.004 0.022 ↑ 1.0 1 1

Hash (cost=19.18..19.18 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.001 0.018 ↑ 1.0 1 1

Nested Loop (cost=0.70..19.18 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)

27. 0.010 0.010 ↑ 1.0 1 1

Index Scan using ixf01_userorganizationrole_t on userorganizationrole_t (cost=0.42..10.46 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_strt_ts <= '2019-07-09 14:00:00.672+00'::time
28. 0.007 0.007 ↑ 1.0 1 1

Index Scan using ixpk_organizationrole_t on organizationrole_t (cost=0.28..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (org_role_uid = userorganizationrole_t.org_role_uid)
  • Filter: ((org_uid = organization_t_2.org_uid) OR (org_uid =organization_t.site_org_uid))
29. 0.111 0.111 ↑ 1.0 1 111

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=111)

  • Index Cond: (role_uid = rolepermission_t.role_uid)
  • Filter: ((eff_strt_ts <= '2019-07-09 14:00:00.672+00'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-07-09 14:00:00.672+00'::timestamp with time zone)))
30. 0.009 0.044 ↑ 1.0 1 1

Aggregate (cost=14.04..14.05 rows=1 width=32) (actual time=0.044..0.044 rows=1 loops=1)

31. 0.016 0.035 ↑ 1.2 8 1

Sort (cost=13.88..13.91 rows=10 width=11) (actual time=0.034..0.035 rows=8 loops=1)

  • Sort Key: userdshb_t.dflt_dshb_sw DESC, userdshb_t.eff_stdt
  • Sort Method: quicksort Memory: 25kB
32. 0.019 0.019 ↑ 1.2 8 1

Index Scan using ixf01_userdshb_t on userdshb_t (cost=0.43..13.72 rows=10 width=11) (actual time=0.013..0.019 rows=8 loops=1)

  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_stdt <= '2019-07-09'::date) AND ((eff_endt IS NULL) OR (eff_endt > '2019-07-09'::date)))
Planning time : 2.013 ms
Execution time : 5.039 ms