explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 11P

Settings
# exclusive inclusive rows x rows loops node
1. 41.929 41.929 ↑ 2.2 991 1

CTE Scan on normalised_data (cost=5,731.10..5,774.48 rows=2,169 width=40) (actual time=37.872..41.929 rows=991 loops=1)

2.          

CTE trial_orgs

3. 0.076 0.164 ↑ 1.4 21 1

Hash Join (cost=3.77..20.15 rows=30 width=8) (actual time=0.041..0.164 rows=21 loops=1)

  • Hash Cond: (org_ext_func.extended_function_id = ext_funcs.extended_function_id)
4. 0.069 0.069 ↓ 1.0 892 1

Seq Scan on organisation_extended_funcs org_ext_func (cost=0.00..13.88 rows=888 width=8) (actual time=0.009..0.069 rows=892 loops=1)

5. 0.003 0.019 ↑ 2.0 1 1

Hash (cost=3.75..3.75 rows=2 width=4) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.016 0.016 ↑ 2.0 1 1

Seq Scan on extended_functions ext_funcs (cost=0.00..3.75 rows=2 width=4) (actual time=0.010..0.016 rows=1 loops=1)

  • Filter: ((role_name)::text = ANY ('{TRIAL_PUB_TM,CM_TRIAL}'::text[]))
  • Rows Removed by Filter: 44
7.          

CTE users

8. 0.158 24.200 ↑ 48.9 18 1

HashAggregate (cost=3,179.46..3,188.26 rows=880 width=20) (actual time=24.194..24.200 rows=18 loops=1)

  • Group Key: d.username, u.organisation_id
9. 0.456 24.042 ↑ 1.1 768 1

Hash Join (cost=3,123.58..3,175.06 rows=880 width=20) (actual time=23.626..24.042 rows=768 loops=1)

  • Hash Cond: (d.username = (u.username)::text)
10. 0.267 0.267 ↑ 1.0 991 1

Index Scan using idx_raw_data_repopulate_id on raw_data_repopulate d (cost=0.57..49.33 rows=1,038 width=16) (actual time=0.043..0.267 rows=991 loops=1)

  • Index Cond: ((id >= 10) AND (id <= 1,000))
11. 12.814 23.319 ↑ 1.0 53,625 1

Hash (cost=2,449.67..2,449.67 rows=53,867 width=28) (actual time=23.319..23.319 rows=53,625 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,704kB
12. 10.505 10.505 ↑ 1.0 53,630 1

Seq Scan on users u (cost=0.00..2,449.67 rows=53,867 width=28) (actual time=0.004..10.505 rows=53,630 loops=1)

13.          

CTE normalised_data

14. 1.390 41.507 ↑ 2.2 991 1

Hash Left Join (cost=2,226.89..2,522.69 rows=2,169 width=40) (actual time=37.870..41.507 rows=991 loops=1)

  • Hash Cond: (((org.name)::text = orgd.organisation_name) AND (CASE WHEN (trialorg.extended_function_id IS NOT NULL) THEN true ELSE false END = orgd.trial))
15. 0.389 39.858 ↑ 1.0 991 1

Hash Left Join (cost=2,195.99..2,383.27 rows=1,038 width=158) (actual time=37.591..39.858 rows=991 loops=1)

  • Hash Cond: ((d_1.scope = scd.scope) AND (d_1.context = scd.context))
16. 0.300 39.460 ↑ 1.0 991 1

Hash Left Join (cost=2,194.74..2,376.45 rows=1,038 width=166) (actual time=37.575..39.460 rows=991 loops=1)

  • Hash Cond: (d_1.operation = opd.operation)
17. 0.241 39.150 ↑ 1.0 991 1

Hash Left Join (cost=2,193.43..2,371.59 rows=1,038 width=173) (actual time=37.553..39.150 rows=991 loops=1)

  • Hash Cond: (d_1.object_type = objd.object_type)
18. 0.278 38.896 ↑ 1.0 991 1

Hash Left Join (cost=2,191.84..2,367.16 rows=1,038 width=180) (actual time=37.533..38.896 rows=991 loops=1)

  • Hash Cond: (d_1.app = appd.app)
19. 0.197 38.612 ↑ 1.0 991 1

Hash Left Join (cost=2,190.71..2,363.21 rows=1,038 width=179) (actual time=37.521..38.612 rows=991 loops=1)

  • Hash Cond: ((la.name)::text = lad.location_name)
20. 0.309 38.373 ↑ 1.0 991 1

Hash Left Join (cost=2,187.19..2,356.91 rows=1,038 width=343) (actual time=37.471..38.373 rows=991 loops=1)

  • Hash Cond: (d_1.username = u_1.username)
21. 0.473 11.900 ↑ 1.0 991 1

Hash Left Join (cost=1,043.39..1,094.87 rows=1,038 width=69) (actual time=11.294..11.900 rows=991 loops=1)

  • Hash Cond: (d_1.username = ud.username)
22. 0.276 0.276 ↑ 1.0 991 1

Index Scan using idx_raw_data_repopulate_id on raw_data_repopulate d_1 (cost=0.57..49.33 rows=1,038 width=65) (actual time=0.039..0.276 rows=991 loops=1)

  • Index Cond: ((id >= 10) AND (id <= 1,000))
23. 7.065 11.151 ↓ 1.0 32,505 1

Hash (cost=641.81..641.81 rows=32,081 width=28) (actual time=11.151..11.151 rows=32,505 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,173kB
24. 4.086 4.086 ↓ 1.0 32,505 1

Seq Scan on user_dim ud (cost=0.00..641.81 rows=32,081 width=28) (actual time=0.003..4.086 rows=32,505 loops=1)

25. 0.009 26.164 ↑ 48.9 18 1

Hash (cost=1,132.80..1,132.80 rows=880 width=322) (actual time=26.164..26.164 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
26. 0.011 26.155 ↑ 48.9 18 1

Hash Left Join (cost=1,101.58..1,132.80 rows=880 width=322) (actual time=26.129..26.155 rows=18 loops=1)

  • Hash Cond: (org.organisation_id = trialorg.organisation_id)
27. 0.014 25.968 ↑ 48.9 18 1

Hash Left Join (cost=1,100.60..1,128.21 rows=880 width=322) (actual time=25.946..25.968 rows=18 loops=1)

  • Hash Cond: (org.region_id = region.region_id)
28. 0.014 24.482 ↑ 48.9 18 1

Hash Left Join (cost=37.10..57.02 rows=880 width=158) (actual time=24.465..24.482 rows=18 loops=1)

  • Hash Cond: (u_1.organisation_id = org.organisation_id)
29. 24.209 24.209 ↑ 48.9 18 1

CTE Scan on users u_1 (cost=0.00..17.60 rows=880 width=36) (actual time=24.196..24.209 rows=18 loops=1)

30. 0.124 0.259 ↑ 1.2 681 1

Hash (cost=26.49..26.49 rows=849 width=126) (actual time=0.259..0.259 rows=681 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
31. 0.135 0.135 ↑ 1.2 681 1

Seq Scan on organisation org (cost=0.00..26.49 rows=849 width=126) (actual time=0.004..0.135 rows=681 loops=1)

32. 0.127 1.472 ↓ 2.2 716 1

Hash (cost=1,059.40..1,059.40 rows=328 width=172) (actual time=1.472..1.472 rows=716 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
33. 0.139 1.345 ↓ 2.2 716 1

Hash Right Join (cost=1,051.14..1,059.40 rows=328 width=172) (actual time=0.987..1.345 rows=716 loops=1)

  • Hash Cond: (r_la.region_id = region.region_id)
34. 0.147 1.072 ↓ 2.7 748 1

Hash Left Join (cost=1,032.47..1,040.00 rows=279 width=172) (actual time=0.846..1.072 rows=748 loops=1)

  • Hash Cond: (r_la.la_id = la.la_id)
35. 0.089 0.089 ↓ 2.7 748 1

Seq Scan on region_la r_la (cost=0.00..6.79 rows=279 width=8) (actual time=0.003..0.089 rows=748 loops=1)

36. 0.089 0.836 ↑ 2.8 460 1

Hash (cost=1,016.10..1,016.10 rows=1,310 width=172) (actual time=0.836..0.836 rows=460 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 38kB
37. 0.747 0.747 ↑ 2.8 460 1

Seq Scan on la (cost=0.00..1,016.10 rows=1,310 width=172) (actual time=0.002..0.747 rows=460 loops=1)

38. 0.061 0.134 ↓ 1.4 461 1

Hash (cost=14.57..14.57 rows=328 width=4) (actual time=0.134..0.134 rows=461 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
39. 0.073 0.073 ↓ 1.4 461 1

Seq Scan on region (cost=0.00..14.57 rows=328 width=4) (actual time=0.003..0.073 rows=461 loops=1)

  • Filter: (NOT national_coverage)
  • Rows Removed by Filter: 37
40. 0.006 0.176 ↑ 1.4 21 1

Hash (cost=0.60..0.60 rows=30 width=8) (actual time=0.176..0.176 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.170 0.170 ↑ 1.4 21 1

CTE Scan on trial_orgs trialorg (cost=0.00..0.60 rows=30 width=8) (actual time=0.042..0.170 rows=21 loops=1)

42. 0.025 0.042 ↓ 1.0 116 1

Hash (cost=2.12..2.12 rows=112 width=36) (actual time=0.042..0.042 rows=116 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
43. 0.017 0.017 ↓ 1.0 116 1

Seq Scan on local_authority_dim lad (cost=0.00..2.12 rows=112 width=36) (actual time=0.004..0.017 rows=116 loops=1)

44. 0.003 0.006 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=36) (actual time=0.006..0.006 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on app_dim appd (cost=0.00..1.06 rows=6 width=36) (actual time=0.002..0.003 rows=6 loops=1)

46. 0.007 0.013 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=36) (actual time=0.013..0.013 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
47. 0.006 0.006 ↑ 1.0 26 1

Seq Scan on object_dim objd (cost=0.00..1.26 rows=26 width=36) (actual time=0.003..0.006 rows=26 loops=1)

48. 0.006 0.010 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=36) (actual time=0.010..0.010 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
49. 0.004 0.004 ↑ 1.0 14 1

Seq Scan on operation_dim opd (cost=0.00..1.14 rows=14 width=36) (actual time=0.002..0.004 rows=14 loops=1)

50. 0.006 0.009 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=68) (actual time=0.009..0.009 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on scope_dim scd (cost=0.00..1.10 rows=10 width=68) (actual time=0.002..0.003 rows=10 loops=1)

52. 0.161 0.259 ↑ 1.2 725 1

Hash (cost=18.36..18.36 rows=836 width=37) (actual time=0.259..0.259 rows=725 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
53. 0.098 0.098 ↑ 1.2 725 1

Seq Scan on organisation_dim orgd (cost=0.00..18.36 rows=836 width=37) (actual time=0.003..0.098 rows=725 loops=1)

Planning time : 3.975 ms
Execution time : 42.751 ms