explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5mkL

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 5,906.845 ↓ 3.0 600 1

Unique (cost=2,023,924.80..2,023,927.62 rows=200 width=14) (actual time=5,906.730..5,906.845 rows=600 loops=1)

2. 0.287 5,906.757 ↓ 1.1 600 1

Sort (cost=2,023,924.80..2,023,926.21 rows=565 width=14) (actual time=5,906.730..5,906.757 rows=600 loops=1)

  • Sort Key: gr1.users_names
  • Sort Method: quicksort Memory: 53kB
3. 19.315 5,906.470 ↓ 1.1 600 1

Hash Semi Join (cost=1,594,290.05..2,023,898.97 rows=565 width=14) (actual time=4,627.181..5,906.470 rows=600 loops=1)

  • Hash Cond: (wg_.r_object_id = dm_group_r.r_object_id)
4. 55.997 5,886.846 ↑ 46.9 187,608 1

Hash Join (cost=1,594,202.13..2,000,731.20 rows=8,789,929 width=31) (actual time=4,480.776..5,886.846 rows=187,608 loops=1)

  • Hash Cond: ((gr2.i_supergroups_names)::text = (wg_.group_name)::text)
5. 32.709 5,824.119 ↑ 46.4 189,624 1

Merge Join (cost=1,593,331.30..1,878,998.85 rows=8,789,929 width=44) (actual time=4,473.970..5,824.119 rows=189,624 loops=1)

  • Merge Cond: ((ig_.user_name)::text = (gr1.users_names)::text)
6. 11.026 16.445 ↑ 1.0 17,556 1

Sort (cost=2,222.18..2,266.91 rows=17,889 width=30) (actual time=15.256..16.445 rows=17,556 loops=1)

  • Sort Key: ig_.user_name
  • Sort Method: quicksort Memory: 2,077kB
7. 5.419 5.419 ↑ 1.0 17,583 1

Seq Scan on dm_user_s ig_ (cost=0.00..958.61 rows=17,889 width=30) (actual time=0.012..5.419 rows=17,583 loops=1)

  • Filter: (user_state = 0)
8. 12.836 5,774.965 ↑ 46.3 189,624 1

Materialize (cost=1,591,109.11..1,766,799.59 rows=8,784,524 width=44) (actual time=4,458.640..5,774.965 rows=189,624 loops=1)

9. 485.547 5,762.129 ↑ 127.0 69,144 1

Unique (cost=1,591,109.11..1,656,993.04 rows=8,784,524 width=44) (actual time=4,458.637..5,762.129 rows=69,144 loops=1)

10. 3,645.904 5,276.582 ↑ 1.7 5,256,693 1

Sort (cost=1,591,109.11..1,613,070.42 rows=8,784,524 width=44) (actual time=4,458.635..5,276.582 rows=5,256,693 loops=1)

  • Sort Key: gr1.users_names, gr2.i_supergroups_names
  • Sort Method: external merge Disk: 109,944kB
11. 684.567 1,630.678 ↑ 1.7 5,256,693 1

Nested Loop (cost=0.41..37,510.57 rows=8,784,524 width=44) (actual time=0.026..1,630.678 rows=5,256,693 loops=1)

12. 7.898 7.898 ↑ 1.1 55,189 1

Seq Scan on dm_group_r gr1 (cost=0.00..1,327.49 rows=61,263 width=31) (actual time=0.009..7.898 rows=55,189 loops=1)

  • Filter: (users_names IS NOT NULL)
  • Rows Removed by Filter: 3,350
13. 938.213 938.213 ↓ 23.8 95 55,189

Index Scan using d_1f01e24080000109 on dm_group_r gr2 (cost=0.41..0.55 rows=4 width=47) (actual time=0.002..0.017 rows=95 loops=55,189)

  • Index Cond: (r_object_id = gr1.r_object_id)
14. 3.756 6.730 ↓ 1.0 15,553 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,368kB
15. 2.974 2.974 ↓ 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.007..2.974 rows=15,553 loops=1)

16. 0.006 0.309 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.004 0.303 ↑ 1.0 1 1

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

18. 0.004 0.279 ↑ 1.0 1 1

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

  • Group Key: (ccc_.alias_value)::text
19. 0.004 0.275 ↑ 1.0 1 1

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

20. 0.009 0.244 ↑ 1.0 1 1

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

21. 0.006 0.202 ↓ 3.7 11 1

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

22. 0.042 0.156 ↓ 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.097..0.156 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
23.          

SubPlan (for Index Scan)

24. 0.012 0.114 ↑ 1.0 1 3

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

25. 0.042 0.042 ↑ 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.014..0.014 rows=1 loops=3)

  • Index Cond: ((wb_.acl_name)::text = (object_name)::text)
  • Filter: ((wb_.acl_domain)::text = (owner_name)::text)
26. 0.060 0.060 ↑ 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.020..0.020 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
27. 0.040 0.040 ↓ 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.019..0.020 rows=6 loops=2)

  • Index Cond: (r_object_id = wb_.r_object_id)
28. 0.033 0.033 ↓ 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.003..0.003 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
29. 0.027 0.027 ↑ 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.027..0.027 rows=1 loops=1)

  • Index Cond: (r_object_id = xb_.r_object_id)
  • Filter: ((dictionary_name)::text ~~ 'GMP Applicable Sites'::text)
30. 0.020 0.020 ↑ 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.020..0.020 rows=1 loops=1)

  • Index Cond: ((i_supergroups_names)::text = (ccc_.alias_value)::text)