explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pgnD

Settings
# exclusive inclusive rows x rows loops node
1. 1.018 519.830 ↑ 1.7 66 1

Hash Left Join (cost=333.56..206,949.50 rows=111 width=300) (actual time=8.405..519.830 rows=66 loops=1)

  • Hash Cond: ((organization_t.st_cd)::text = (organization_t_2.st_cd)::text)
2. 0.117 1.227 ↓ 1.2 66 1

Nested Loop Left Join (cost=169.51..345.00 rows=54 width=202) (actual time=0.153..1.227 rows=66 loops=1)

3. 0.324 0.978 ↓ 1.2 66 1

Merge Join (cost=169.23..322.96 rows=54 width=157) (actual time=0.148..0.978 rows=66 loops=1)

  • Merge Cond: (organization_t.org_uid = userorganization_t.org_uid)
4. 0.568 0.568 ↑ 1.8 2,517 1

Index Scan using ixpk_organization_t on organization_t (cost=0.28..264.53 rows=4,508 width=73) (actual time=0.005..0.568 rows=2,517 loops=1)

5. 0.051 0.086 ↓ 1.2 66 1

Sort (cost=168.95..169.08 rows=54 width=88) (actual time=0.065..0.086 rows=66 loops=1)

  • Sort Key: userorganization_t.org_uid
  • Sort Method: quicksort Memory: 34kB
6. 0.035 0.035 ↓ 1.2 66 1

Index Scan using ixf02_useruid on userorganization_t (cost=0.29..167.40 rows=54 width=88) (actual time=0.009..0.035 rows=66 loops=1)

  • Index Cond: (user_uid = 15)
  • Filter: (eff_endt IS NULL)
7. 0.132 0.132 ↑ 1.0 1 66

Index Scan using ixpk_organization_t on organization_t organization_t_1 (cost=0.28..0.41 rows=1 width=49) (actual time=0.002..0.002 rows=1 loops=66)

  • Index Cond: (organization_t.site_org_uid = org_uid)
8. 0.011 0.541 ↑ 1.0 56 1

Hash (cost=163.35..163.35 rows=56 width=30) (actual time=0.541..0.541 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
9. 0.530 0.530 ↑ 1.0 56 1

Seq Scan on organization_t organization_t_2 (cost=0.00..163.35 rows=56 width=30) (actual time=0.006..0.530 rows=56 loops=1)

  • Filter: ((org_typ_cd)::text = 'T'::text)
  • Rows Removed by Filter: 4457
10.          

SubPlan (forHash Left Join)

11. 8.844 516.252 ↓ 4.0 105 66

Sort (cost=1,850.86..1,850.93 rows=26 width=20) (actual time=7.817..7.822 rows=105 loops=66)

  • Sort Key: independentpermission_t.prmis_cd
  • Sort Method: quicksort Memory: 30kB
12. 2.112 507.408 ↓ 4.0 105 66

HashAggregate (cost=1,849.99..1,850.25 rows=26 width=20) (actual time=7.677..7.688 rows=105 loops=66)

  • Group Key: independentpermission_t.prmis_cd
13. 0.726 505.296 ↓ 4.3 112 66

Append (cost=0.00..1,849.93 rows=26 width=20) (actual time=0.464..7.656 rows=112 loops=66)

14. 195.756 195.756 ↑ 1.0 2 66

Seq Scan on independentpermission_t (cost=0.00..1,089.84 rows=2 width=5) (actual time=0.462..2.966 rows=2 loops=66)

  • Filter: (user_org_uid = userorganization_t.user_org_uid)
  • Rows Removed by Filter: 47793
15. 1.091 308.814 ↓ 4.6 110 66

Nested Loop (cost=17.26..759.70 rows=24 width=5) (actual time=1.417..4.679 rows=110 loops=66)

  • Join Filter: (organizationrole_t.role_uid = dcpsrole_t.role_uid)
16. 160.908 300.432 ↓ 5.0 110 66

Hash Join (cost=16.98..752.75 rows=22 width=13) (actual time=1.412..4.552 rows=110 loops=66)

  • Hash Cond: (rolepermission_t.role_uid = organizationrole_t.role_uid)
17. 138.930 138.930 ↑ 1.0 31,616 66

Seq Scan on rolepermission_t (cost=0.00..616.22 rows=31,822 width=9) (actual time=0.002..2.105 rows=31,616 loops=66)

18. 0.066 0.594 ↑ 1.0 1 66

Hash (cost=16.97..16.97 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=66)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.106 0.528 ↑ 1.0 1 66

Nested Loop (cost=0.57..16.97 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=66)

20. 0.264 0.264 ↑ 1.0 1 66

Index Scan using ixf01_userorganizationrole_t on userorganizationrole_t (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=66)

  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_strt_ts <= '2019-11-04 16:41:04.375+00'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-11-04 16:41:04.375+00'::timestamp with time zone)))
21. 0.158 0.158 ↑ 1.0 1 79

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

  • 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))
22. 7.291 7.291 ↑ 1.0 1 7,291

Index Scan using ixpk_dcpsrole_t on dcpsrole_t (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=7,291)

  • Index Cond: (role_uid = rolepermission_t.role_uid)
  • Filter: ((eff_strt_ts <= '2019-11-04 16:41:04.375+00'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-11-04 16:41:04.375+00'::timestamp with time zone)))
23. 0.330 0.792 ↑ 1.1 8 66

Sort (cost=8.83..8.85 rows=9 width=11) (actual time=0.012..0.012 rows=8 loops=66)

  • Sort Key: userdshb_t.dflt_dshb_sw DESC, userdshb_t.eff_stdt
  • Sort Method: quicksort Memory: 25kB
24. 0.462 0.462 ↑ 1.1 8 66

Index Scan using ixf01_userdshb_t on userdshb_t (cost=0.42..8.69 rows=9 width=11) (actual time=0.004..0.007 rows=8 loops=66)

  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_stdt <= '2019-11-04'::date) AND ((eff_endt IS NULL) OR (eff_endt > '2019-11-04'::date)))
Planning time : 1.144 ms
Execution time : 519.939 ms