explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8bkT

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 52.012 ↑ 1.0 1 1

Unique (cost=4,543.42..4,543.42 rows=1 width=14) (actual time=52.012..52.012 rows=1 loops=1)

2. 0.020 52.011 ↑ 1.0 1 1

Sort (cost=4,543.42..4,543.42 rows=1 width=14) (actual time=52.010..52.011 rows=1 loops=1)

  • Sort Key: gr1.users_names
  • Sort Method: quicksort Memory: 25kB
3. 1.544 51.991 ↑ 1.0 1 1

Hash Semi Join (cost=3,685.91..4,543.41 rows=1 width=14) (actual time=49.026..51.991 rows=1 loops=1)

  • Hash Cond: (wg_.r_object_id = dm_group_r.r_object_id)
4. 3.313 50.211 ↑ 1.0 15,171 1

Hash Join (cost=3,597.99..4,414.62 rows=15,563 width=31) (actual time=39.621..50.211 rows=15,171 loops=1)

  • Hash Cond: ((gr1.users_names)::text = (ig_.user_name)::text)
5. 4.963 37.314 ↑ 1.0 15,171 1

Hash Join (cost=2,414.16..2,939.08 rows=15,553 width=31) (actual time=29.904..37.314 rows=15,171 loops=1)

  • Hash Cond: ((gr1.i_supergroups_names)::text = (wg_.group_name)::text)
6. 16.754 25.209 ↓ 1.1 17,174 1

HashAggregate (cost=1,543.33..1,698.86 rows=15,553 width=44) (actual time=22.681..25.209 rows=17,174 loops=1)

  • Group Key: gr1.users_names, gr1.i_supergroups_names
7. 8.455 8.455 ↑ 1.0 55,189 1

Seq Scan on dm_group_r gr1 (cost=0.00..1,267.39 rows=55,189 width=44) (actual time=0.008..8.455 rows=55,189 loops=1)

  • Filter: (users_names IS NOT NULL)
  • Rows Removed by Filter: 3,350
8. 3.912 7.142 ↓ 1.0 15,553 1

Hash (cost=676.48..676.48 rows=15,548 width=49) (actual time=7.142..7.142 rows=15,553 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,368kB
9. 3.230 3.230 ↓ 1.0 15,553 1

Seq Scan on dm_group_s wg_ (cost=0.00..676.48 rows=15,548 width=49) (actual time=0.006..3.230 rows=15,553 loops=1)

10. 3.452 9.584 ↑ 1.0 17,583 1

Hash (cost=959.91..959.91 rows=17,913 width=30) (actual time=9.584..9.584 rows=17,583 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,323kB
11. 6.132 6.132 ↑ 1.0 17,583 1

Seq Scan on dm_user_s ig_ (cost=0.00..959.91 rows=17,913 width=30) (actual time=0.010..6.132 rows=17,583 loops=1)

  • Filter: (user_state = 0)
12. 0.003 0.236 ↑ 1.0 1 1

Hash (cost=87.91..87.91 rows=1 width=17) (actual time=0.236..0.236 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.004 0.233 ↑ 1.0 1 1

Nested Loop (cost=79.87..87.91 rows=1 width=17) (actual time=0.232..0.233 rows=1 loops=1)

14. 0.007 0.215 ↑ 1.0 1 1

HashAggregate (cost=79.46..79.47 rows=1 width=17) (actual time=0.215..0.215 rows=1 loops=1)

  • Group Key: (ccc_.alias_value)::text
15. 0.004 0.208 ↑ 1.0 1 1

Nested Loop (cost=1.69..79.45 rows=1 width=17) (actual time=0.150..0.208 rows=1 loops=1)

16. 0.004 0.192 ↑ 1.0 1 1

Nested Loop (cost=1.41..79.14 rows=1 width=68) (actual time=0.134..0.192 rows=1 loops=1)

17. 0.007 0.144 ↓ 3.7 11 1

Nested Loop (cost=0.99..77.81 rows=3 width=36) (actual time=0.091..0.144 rows=11 loops=1)

18. 0.032 0.107 ↓ 2.0 2 1

Index Scan using d_1f01e2408000000f on dm_sysobject_s wb_ (cost=0.43..53.48 rows=1 width=45) (actual time=0.069..0.107 rows=2 loops=1)

  • Index Cond: ((object_name)::text = 'Boston'::text)
  • Filter: ((i_is_deleted = 0) AND (i_has_folder = 1) AND (((owner_name)::text = ANY ('{gmpuser2,cd_md,cd_boston_authors,cd_gmp_all_users,dm_world,cd_boston_approvers,cd_boston_reviewers,cd_gmp_authors,cd_boston_users,cd_md_submission_users,[group_name],cd_boston_qo_approvers,cd_boston_coordinators,cd_md_regulatory,ws_lsqm_coordinators}'::text[])) OR (SubPlan 1)))
  • Rows Removed by Filter: 1
19.          

SubPlan (for Index Scan)

20. 0.009 0.075 ↑ 1.0 1 3

Nested Loop (cost=0.86..20.53 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=3)

21. 0.027 0.027 ↑ 1.0 1 3

Index Scan using d_1f01e24080000990 on dm_acl_s acl_s0 (cost=0.43..8.45 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=3)

  • Index Cond: ((wb_.acl_name)::text = (object_name)::text)
  • Filter: ((wb_.acl_domain)::text = (owner_name)::text)
22. 0.039 0.039 ↑ 1.0 1 3

Index Scan using d_1f01e24080000991 on dm_acl_r acl_r (cost=0.43..12.07 rows=1 width=17) (actual time=0.013..0.013 rows=1 loops=3)

  • Index Cond: (r_object_id = acl_s0.r_object_id)
  • Filter: (((r_permit_type = 0) OR (r_permit_type IS NULL)) AND (r_accessor_permit >= 2) AND (((r_accessor_name)::text = ANY ('{gmpuser2,dm_world}'::text[])) OR ((r_is_group = 1) AND ((r_accessor_name)::text = ANY ('{gmpuser2,cd_md,cd_boston_authors,cd_gmp_all_users,dm_world,cd_boston_approvers,cd_boston_reviewers,cd_gmp_authors,cd_boston_users,cd_md_submission_users,[group_name],cd_boston_qo_approvers,cd_boston_coordinators,cd_md_regulatory,ws_lsqm_coordinators}'::text[])))))
  • Rows Removed by Filter: 3
23. 0.030 0.030 ↓ 1.2 6 2

Index Scan using d_1f01e24080000055 on dm_sysobject_r xb_ (cost=0.56..24.28 rows=5 width=19) (actual time=0.014..0.015 rows=6 loops=2)

  • Index Cond: (r_object_id = wb_.r_object_id)
24. 0.044 0.044 ↓ 0.0 0 11

Index Scan using d_1f01e2408000053c on d2_dictionary_value_r ccc_ (cost=0.42..0.44 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: ((r_object_id = xb_.r_object_id) AND (i_position = xb_.i_position))
  • Filter: (((alias_value)::text <> ' '::text) AND ((alias_value)::text ~~ '%_authors'::text))
  • Rows Removed by Filter: 1
25. 0.012 0.012 ↑ 1.0 1 1

Index Scan using d_1f01e2408000053b on d2_dictionary_value_s acc_ (cost=0.29..0.31 rows=1 width=17) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (r_object_id = xb_.r_object_id)
  • Filter: ((dictionary_name)::text ~~ 'GMP Applicable Sites'::text)
26. 0.014 0.014 ↑ 1.0 1 1

Index Scan using d_1f01e24080000031 on dm_group_r (cost=0.41..8.43 rows=1 width=47) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((i_supergroups_names)::text = (ccc_.alias_value)::text)
Planning time : 24.976 ms
Execution time : 52.493 ms