explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vsUN

Settings
# exclusive inclusive rows x rows loops node
1. 0.279 502.283 ↑ 1.7 67 1

Hash Left Join (cost=165.47..207,939.62 rows=111 width=318) (actual time=8.874..502.283 rows=67 loops=1)

  • Hash Cond: ((organization_t.st_cd)::text = (organization_t_2.st_cd)::text)
2. 0.057 0.780 ↓ 1.2 67 1

Nested Loop Left Join (cost=1.42..243.86 rows=54 width=227) (actual time=0.053..0.780 rows=67 loops=1)

3. 0.079 0.589 ↓ 1.2 67 1

Nested Loop Left Join (cost=1.14..221.81 rows=54 width=182) (actual time=0.045..0.589 rows=67 loops=1)

4. 0.128 0.376 ↓ 1.2 67 1

Nested Loop (cost=0.85..202.46 rows=54 width=172) (actual time=0.037..0.376 rows=67 loops=1)

5. 0.039 0.114 ↓ 1.2 67 1

Nested Loop (cost=0.56..185.61 rows=54 width=99) (actual time=0.027..0.114 rows=67 loops=1)

6. 0.011 0.011 ↑ 1.0 1 1

Index Scan using ixf02_pin on dcpsuser_t (cost=0.28..8.29 rows=1 width=11) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: ((pin)::text = '141685'::text)
7. 0.064 0.064 ↓ 1.2 67 1

Index Scan using ixf02_useruid on userorganization_t (cost=0.29..176.75 rows=57 width=92) (actual time=0.014..0.064 rows=67 loops=1)

  • Index Cond: (user_uid = dcpsuser_t.user_uid)
  • Filter: (eff_endt IS NULL)
8. 0.134 0.134 ↑ 1.0 1 67

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.31 rows=1 width=77) (actual time=0.002..0.002 rows=1 loops=67)

  • Index Cond: (org_uid = userorganization_t.org_uid)
9. 0.134 0.134 ↑ 1.0 1 67

Index Scan using userwkldtrld_t_user_org_uid_idx on userwkldtrld_t (cost=0.29..0.36 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=67)

  • Index Cond: (userorganization_t.user_org_uid = user_org_uid)
10. 0.134 0.134 ↑ 1.0 1 67

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

  • Index Cond: (organization_t.site_org_uid = org_uid)
11. 0.016 0.533 ↑ 1.0 56 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.517 0.517 ↑ 1.0 56 1

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

  • Filter: ((org_typ_cd)::text = 'T'::text)
  • Rows Removed by Filter: 4471
13.          

SubPlan (for Hash Left Join)

14. 0.737 499.686 ↑ 1.0 1 67

Aggregate (cost=1,860.62..1,860.63 rows=1 width=32) (actual time=7.458..7.458 rows=1 loops=67)

15. 6.499 498.949 ↓ 7.8 78 67

Sort (cost=1,860.47..1,860.50 rows=10 width=20) (actual time=7.443..7.447 rows=78 loops=67)

  • Sort Key: independentpermission_t.prmis_cd
  • Sort Method: quicksort Memory: 29kB
16. 1.541 492.450 ↓ 7.8 78 67

HashAggregate (cost=1,860.20..1,860.30 rows=10 width=20) (actual time=7.342..7.350 rows=78 loops=67)

  • Group Key: independentpermission_t.prmis_cd
17. 0.469 490.909 ↓ 8.2 82 67

Append (cost=0.00..1,860.18 rows=10 width=20) (actual time=3.056..7.327 rows=82 loops=67)

18. 0.067 193.697 ↓ 2.0 2 67

Result (cost=0.00..1,103.20 rows=1 width=5) (actual time=2.833..2.891 rows=2 loops=67)

  • One-Time Filter: ((dcpsuser_t.pin)::text = '141685'::text)
19. 0.600 193.630 ↓ 2.0 2 67

Nested Loop (cost=0.00..1,103.20 rows=1 width=5) (actual time=2.833..2.890 rows=2 loops=67)

  • Join Filter: ((independentpermission_t.prmis_cd)::text = (permission_t.prmis_cd)::text)
  • Rows Removed by Join Filter: 107
20. 3.292 191.620 ↓ 2.0 2 67

Nested Loop (cost=0.00..1,095.40 rows=1 width=10) (actual time=2.824..2.860 rows=2 loops=67)

  • Join Filter: ((independentpermission_t.prmis_cd)::text = (tnantprmisassocn_t.prmis_cd)::text)
  • Rows Removed by Join Filter: 238
21. 0.938 0.938 ↓ 1.8 80 67

Seq Scan on tnantprmisassocn_t (cost=0.00..3.64 rows=44 width=5) (actual time=0.003..0.014 rows=80 loops=67)

  • Filter: ((saas_tnant_cd)::text = (userorganization_t.saas_tnant_cd)::text)
  • Rows Removed by Filter: 51
22. 0.000 187.390 ↓ 1.5 3 5,354

Materialize (cost=0.00..1,090.45 rows=2 width=5) (actual time=0.005..0.035 rows=3 loops=5,354)

23. 187.734 187.734 ↓ 1.5 3 67

Seq Scan on independentpermission_t (cost=0.00..1,090.44 rows=2 width=5) (actual time=0.368..2.802 rows=3 loops=67)

  • Filter: (user_org_uid = userorganization_t.user_org_uid)
  • Rows Removed by Filter: 47792
24. 1.410 1.410 ↑ 2.5 52 141

Seq Scan on permission_t (cost=0.00..6.18 rows=130 width=5) (actual time=0.001..0.010 rows=52 loops=141)

  • Filter: ((eff_endt IS NULL) OR (eff_endt > '2019-11-04'::date))
  • Rows Removed by Filter: 40
25. 0.469 296.743 ↓ 8.8 79 67

Result (cost=21.45..756.83 rows=9 width=5) (actual time=1.283..4.429 rows=79 loops=67)

  • One-Time Filter: ((dcpsuser_t.pin)::text = '141685'::text)
26. 0.639 296.274 ↓ 8.8 79 67

Nested Loop (cost=21.45..756.83 rows=9 width=5) (actual time=1.283..4.422 rows=79 loops=67)

  • Join Filter: (organizationrole_t.role_uid = dcpsrole_t.role_uid)
27. 1.474 290.311 ↓ 9.9 79 67

Hash Join (cost=21.17..754.30 rows=8 width=13) (actual time=1.279..4.333 rows=79 loops=67)

  • Hash Cond: ((rolepermission_t.prmis_cd)::text = (tnantprmisassocn_t_1.prmis_cd)::text)
28. 153.229 287.162 ↓ 5.0 110 67

Hash Join (cost=16.98..749.92 rows=22 width=13) (actual time=1.249..4.286 rows=110 loops=67)

  • Hash Cond: (rolepermission_t.role_uid = organizationrole_t.role_uid)
29. 133.330 133.330 ↑ 1.0 31,616 67

Seq Scan on rolepermission_t (cost=0.00..614.16 rows=31,616 width=9) (actual time=0.002..1.990 rows=31,616 loops=67)

30. 0.067 0.603 ↑ 1.0 1 67

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.536 0.536 ↑ 1.0 1 67

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

  • -> Index Scan using ixf01_userorganizationrole_t on userorganizationrole_t (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0
  • Index Cond: (user_org_uid = userorganization_t.user_org_uid)
  • Filter: ((eff_strt_ts <= '2019-11-04 14:21:33.311+00'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '
  • -> Index Scan using ixpk_organizationrole_t on organizationrole_t (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.003 rows
  • 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))
32. 0.670 1.675 ↓ 1.8 80 67

Hash (cost=3.64..3.64 rows=44 width=5) (actual time=0.025..0.025 rows=80 loops=67)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
33. 1.005 1.005 ↓ 1.8 80 67

Seq Scan on tnantprmisassocn_t tnantprmisassocn_t_1 (cost=0.00..3.64 rows=44 width=5) (actual time=0.003..0.015 rows=80 loops=67)

  • Filter: ((saas_tnant_cd)::text = (userorganization_t.saas_tnant_cd)::text)
  • Rows Removed by Filter: 51
34. 5.324 5.324 ↑ 1.0 1 5,324

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=5,324)

  • Index Cond: (role_uid = rolepermission_t.role_uid)
  • Filter: ((eff_strt_ts <= '2019-11-04 14:21:33.311+00'::timestamp with time zone) AND ((eff_end_ts IS NULL) OR (eff_end_ts > '2019-11-04 14:21:33.311
35. 0.134 1.005 ↑ 1.0 1 67

Aggregate (cost=8.97..8.98 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=67)

36. 0.335 0.871 ↑ 1.1 8 67

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

  • Sort Key: userdshb_t.dflt_dshb_sw DESC, userdshb_t.eff_stdt
  • Sort Method: quicksort Memory: 25kB
37. 0.134 0.536 ↑ 1.1 8 67

Result (cost=0.42..8.69 rows=9 width=11) (actual time=0.005..0.008 rows=8 loops=67)

  • One-Time Filter: ((dcpsuser_t.pin)::text = '141685'::text)
38. 0.402 0.402 ↑ 1.1 8 67

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

  • 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 : 2.944 ms
Execution time : 502.511 ms