explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vBwB

Settings
# exclusive inclusive rows x rows loops node
1. 18,348.872 18,348.872 ↑ 5,567.9 201 1

CTE Scan on data (cost=1,513,030.41..1,535,413.53 rows=1,119,156 width=385) (actual time=18,348.181..18,348.872 rows=201 loops=1)

2.          

CTE expanded

3. 0.094 9.404 ↑ 9,992.5 112 1

Unique (cost=432,838.78..472,009.24 rows=1,119,156 width=245) (actual time=9.297..9.404 rows=112 loops=1)

4. 0.326 9.310 ↑ 8,229.1 136 1

Sort (cost=432,838.78..435,636.67 rows=1,119,156 width=245) (actual time=9.296..9.310 rows=136 loops=1)

  • Sort Key: ((string_to_array(my_data_source_preferences.id, ':'::text))[1]), (((string_to_array(my_data_source_preferences.id, ':'::text))[2])::integer), my_data_source_preferences.name, i.id, (COALESCE(ig.name, i.name)), i.name, isl.name, isl.id, i.description, i.indicator_group_short_name, i.sort_order, cm.organization_id, (CASE WHEN (iaf.account_id IS NULL) THEN false ELSE true END)
  • Sort Method: quicksort Memory: 75kB
5. 0.028 8.984 ↑ 8,229.1 136 1

Append (cost=160.35..60,277.29 rows=1,119,156 width=245) (actual time=8.470..8.984 rows=136 loops=1)

6. 0.303 8.947 ↑ 8,227.9 136 1

Hash Left Join (cost=160.35..43,330.90 rows=1,118,989 width=324) (actual time=8.468..8.947 rows=136 loops=1)

  • Hash Cond: (i.id = iaf.indicator_id)
7. 0.096 8.604 ↑ 8,227.9 136 1

Hash Join (cost=154.41..27,913.49 rows=1,118,989 width=319) (actual time=8.411..8.604 rows=136 loops=1)

  • Hash Cond: (ic.channel_id = ch.id)
8. 0.072 8.486 ↑ 14,398.8 136 1

Merge Join (cost=144.94..9,370.77 rows=1,958,231 width=319) (actual time=8.365..8.486 rows=136 loops=1)

  • Merge Cond: (sd.id = d.id)
9. 0.027 2.766 ↑ 57.6 34 1

Merge Join (cost=144.55..175.91 rows=1,958 width=263) (actual time=2.734..2.766 rows=34 loops=1)

  • Merge Cond: (idd.domain_id = sd.id)
10. 0.208 1.677 ↑ 11.2 35 1

Sort (cost=84.47..85.45 rows=392 width=259) (actual time=1.672..1.677 rows=35 loops=1)

  • Sort Key: idd.domain_id
  • Sort Method: quicksort Memory: 151kB
11. 0.130 1.469 ↓ 1.1 416 1

Hash Left Join (cost=50.29..67.59 rows=392 width=259) (actual time=0.913..1.469 rows=416 loops=1)

  • Hash Cond: (i.indicator_group_id = ig.id)
12. 0.164 1.322 ↓ 1.1 416 1

Hash Join (cost=48.75..64.99 rows=392 width=231) (actual time=0.889..1.322 rows=416 loops=1)

  • Hash Cond: (i.indicator_security_level_id = isl.id)
13. 0.214 1.153 ↓ 1.1 416 1

Hash Join (cost=47.71..60.73 rows=392 width=199) (actual time=0.878..1.153 rows=416 loops=1)

  • Hash Cond: (idd.indicator_id = i.id)
14. 0.075 0.075 ↓ 1.0 445 1

Seq Scan on indicator_domain idd (cost=0.00..7.44 rows=444 width=8) (actual time=0.007..0.075 rows=445 loops=1)

15. 0.182 0.864 ↓ 1.0 391 1

Hash (cost=42.84..42.84 rows=389 width=199) (actual time=0.864..0.864 rows=391 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 88kB
16. 0.196 0.682 ↓ 1.0 391 1

Hash Join (cost=34.92..42.84 rows=389 width=199) (actual time=0.440..0.682 rows=391 loops=1)

  • Hash Cond: (ic.indicator_id = i.id)
17. 0.060 0.060 ↓ 1.0 391 1

Seq Scan on indicator_channel ic (cost=0.00..6.89 rows=389 width=8) (actual time=0.006..0.060 rows=391 loops=1)

18. 0.176 0.426 ↑ 1.0 426 1

Hash (cost=29.41..29.41 rows=441 width=191) (actual time=0.426..0.426 rows=426 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 90kB
19. 0.250 0.250 ↑ 1.0 426 1

Seq Scan on indicator i (cost=0.00..29.41 rows=441 width=191) (actual time=0.005..0.250 rows=426 loops=1)

20. 0.001 0.005 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.005..0.005 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on indicator_security_level isl (cost=0.00..1.02 rows=2 width=36) (actual time=0.003..0.004 rows=2 loops=1)

22. 0.007 0.017 ↓ 1.0 25 1

Hash (cost=1.24..1.24 rows=24 width=36) (actual time=0.017..0.017 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.010 0.010 ↓ 1.0 25 1

Seq Scan on indicator_group ig (cost=0.00..1.24 rows=24 width=36) (actual time=0.005..0.010 rows=25 loops=1)

24. 0.007 1.062 ↑ 1,000.0 1 1

Sort (cost=60.08..62.58 rows=1,000 width=4) (actual time=1.058..1.062 rows=1 loops=1)

  • Sort Key: sd.id
  • Sort Method: quicksort Memory: 25kB
25. 1.055 1.055 ↑ 1,000.0 1 1

Function Scan on subdomains sd (cost=0.25..10.25 rows=1,000 width=4) (actual time=1.054..1.055 rows=1 loops=1)

26. 0.022 5.648 ↑ 379.3 145 1

Materialize (cost=0.39..1,250.72 rows=55,000 width=68) (actual time=5.618..5.648 rows=145 loops=1)

27. 0.006 5.626 ↑ 4,230.8 13 1

Nested Loop (cost=0.39..1,113.22 rows=55,000 width=68) (actual time=5.617..5.626 rows=13 loops=1)

28. 0.008 0.008 ↑ 13.8 4 1

Index Only Scan using pk_domain on domain d (cost=0.14..12.97 rows=55 width=4) (actual time=0.005..0.008 rows=4 loops=1)

  • Heap Fetches: 4
29. 5.612 5.612 ↑ 333.3 3 4

Function Scan on my_data_source_preferences (cost=0.25..10.25 rows=1,000 width=64) (actual time=1.402..1.403 rows=3 loops=4)

30. 0.003 0.022 ↓ 1.5 6 1

Hash (cost=9.42..9.42 rows=4 width=8) (actual time=0.022..0.022 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.002 0.019 ↓ 1.5 6 1

Nested Loop (cost=0.28..9.42 rows=4 width=8) (actual time=0.016..0.019 rows=6 loops=1)

32. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_organization_member on organization_member cm (cost=0.28..8.31 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 993)
  • Filter: ((account_id = 453) AND (member_status_id = 'A'::bpchar))
33. 0.007 0.007 ↓ 1.5 6 1

Seq Scan on channel ch (cost=0.00..1.07 rows=4 width=4) (actual time=0.005..0.007 rows=6 loops=1)

  • Filter: (active IS TRUE)
  • Rows Removed by Filter: 1
34. 0.001 0.040 ↓ 0.0 0 1

Hash (cost=5.93..5.93 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
35. 0.039 0.039 ↓ 0.0 0 1

Seq Scan on indicator_account_favorite iaf (cost=0.00..5.93 rows=1 width=8) (actual time=0.039..0.039 rows=0 loops=1)

  • Filter: (453 = account_id)
  • Rows Removed by Filter: 338
36. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=104.34..159.05 rows=167 width=324) (actual time=0.009..0.009 rows=0 loops=1)

37. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on domain d_1 (cost=0.00..1.82 rows=1 width=0) (actual time=0.008..0.009 rows=0 loops=1)

  • Filter: ((id = 3) AND (name = 'My Favourites'::text))
  • Rows Removed by Filter: 55
38. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=104.34..153.89 rows=167 width=319) (never executed)

  • Hash Cond: ((ch_1.id = s.channel_id) AND (cm_1.organization_id = s.organization_id))
39. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=7.14..54.04 rows=504 width=327) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.61..15.48 rows=1 width=259) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=6.48..15.28 rows=1 width=255) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.34..15.10 rows=1 width=227) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.21..14.90 rows=1 width=195) (never executed)

  • Join Filter: (iaf_1.indicator_id = i_1.id)
44. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=5.94..14.30 rows=1 width=12) (never executed)

  • Hash Cond: (ic_1.indicator_id = iaf_1.indicator_id)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on indicator_channel ic_1 (cost=0.00..6.89 rows=389 width=8) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.93..5.93 rows=1 width=4) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Seq Scan on indicator_account_favorite iaf_1 (cost=0.00..5.93 rows=1 width=4) (never executed)

  • Filter: (account_id = 453)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_indicator on indicator i_1 (cost=0.27..0.59 rows=1 width=191) (never executed)

  • Index Cond: (id = ic_1.indicator_id)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_indicator_security_level on indicator_security_level isl_1 (cost=0.13..0.17 rows=1 width=36) (never executed)

  • Index Cond: (id = i_1.indicator_security_level_id)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_indicator_group on indicator_group ig_1 (cost=0.14..0.18 rows=1 width=36) (never executed)

  • Index Cond: (id = i_1.indicator_group_id)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_channel on channel ch_1 (cost=0.13..0.18 rows=1 width=4) (never executed)

  • Index Cond: (id = ic_1.channel_id)
  • Filter: (active IS TRUE)
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.53..28.55 rows=1,000 width=68) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_organization_member on organization_member cm_1 (cost=0.28..8.31 rows=1 width=4) (never executed)

  • Index Cond: (id = 993)
  • Filter: ((account_id = 453) AND (member_status_id = 'A'::bpchar))
54. 0.000 0.000 ↓ 0.0 0

Function Scan on my_data_source_preferences my_data_source_preferences_1 (cost=0.25..10.25 rows=1,000 width=64) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Hash (cost=90.04..90.04 rows=477 width=8) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on account_subscription s (cost=12.02..90.04 rows=477 width=8) (never executed)

  • Recheck Cond: (account_id = 453)
  • Filter: (active IS TRUE)
57. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_account_subscription_account_id (cost=0.00..11.90 rows=482 width=0) (never executed)

  • Index Cond: (account_id = 453)
58.          

CTE data

59. 0.458 18,348.647 ↑ 5,567.9 201 1

WindowAgg (cost=990,659.15..1,041,021.17 rows=1,119,156 width=361) (actual time=18,348.176..18,348.647 rows=201 loops=1)

60. 0.424 18,348.189 ↑ 5,567.9 201 1

Sort (cost=990,659.15..993,457.04 rows=1,119,156 width=336) (actual time=18,348.154..18,348.189 rows=201 loops=1)

  • Sort Key: e.data_source_type, e.data_source_id, e.indicator_id, ad.report_period_start DESC, ad.data_date DESC, ad.upload_date DESC
  • Sort Method: quicksort Memory: 121kB
61. 0.130 18,347.765 ↑ 5,567.9 201 1

Hash Join (cost=484,057.34..533,943.66 rows=1,119,156 width=336) (actual time=18,318.552..18,347.765 rows=201 loops=1)

  • Hash Cond: (o.organization_type_id = ot.id)
62. 0.122 18,347.620 ↑ 5,567.9 201 1

Hash Join (cost=484,056.27..526,854.60 rows=1,119,156 width=308) (actual time=18,318.527..18,347.620 rows=201 loops=1)

  • Hash Cond: (om.organization_id = o.id)
63. 0.083 18,347.352 ↑ 5,567.9 201 1

Nested Loop (cost=484,046.04..523,845.56 rows=1,119,156 width=308) (actual time=18,318.373..18,347.352 rows=201 loops=1)

64. 2.144 2.144 ↑ 1.0 1 1

Index Scan using idx_clinic_member_clinic_id on organization_member om (cost=0.28..356.41 rows=1 width=4) (actual time=0.713..2.144 rows=1 loops=1)

  • Filter: (id = 993)
  • Rows Removed by Filter: 4901
65. 0.094 18,345.125 ↑ 5,567.9 201 1

Hash Left Join (cost=484,045.76..512,297.59 rows=1,119,156 width=304) (actual time=18,317.656..18,345.125 rows=201 loops=1)

  • Hash Cond: (cm_2.organization_id = c.id)
66. 0.157 18,344.926 ↑ 5,567.9 201 1

Hash Left Join (cost=484,035.53..509,288.54 rows=1,119,156 width=285) (actual time=18,317.543..18,344.926 rows=201 loops=1)

  • Hash Cond: (e.data_source_id = cm_2.id)
  • Join Filter: (e.data_source_type = 'Member'::text)
  • Rows Removed by Join Filter: 147
67. 1.005 18,342.833 ↑ 5,567.9 201 1

Merge Left Join (cost=483,872.62..506,187.83 rows=1,119,156 width=281) (actual time=18,315.579..18,342.833 rows=201 loops=1)

  • Merge Cond: ((e.data_source_type = dsl.type) AND (e.data_source_id = dsl.id) AND (e.indicator_id = dsl.indicator_id))
  • Join Filter: (dsl.time_period_id = ad.report_period_id)
  • Rows Removed by Join Filter: 2927
68. 6.909 1,747.086 ↑ 5,567.9 201 1

Merge Left Join (cost=483,812.54..497,722.33 rows=1,119,156 width=281) (actual time=1,721.359..1,747.086 rows=201 loops=1)

  • Merge Cond: ((e.data_source_type = ad.data_source_type) AND (e.data_source_id = ad.data_source_id) AND (e.indicator_id = ad.indicator_id))
69. 0.097 9.573 ↑ 9,992.5 112 1

Sort (cost=387,293.61..390,091.50 rows=1,119,156 width=237) (actual time=9.551..9.573 rows=112 loops=1)

  • Sort Key: e.data_source_type, e.data_source_id, e.indicator_id
  • Sort Method: quicksort Memory: 63kB
70. 9.476 9.476 ↑ 9,992.5 112 1

CTE Scan on expanded e (cost=0.00..22,383.12 rows=1,119,156 width=237) (actual time=9.299..9.476 rows=112 loops=1)

71. 6.768 1,730.604 ↑ 7.9 26,567 1

Materialize (cost=96,518.93..97,570.69 rows=210,352 width=67) (actual time=1,711.640..1,730.604 rows=26,567 loops=1)

72. 716.909 1,723.836 ↑ 7.9 26,567 1

Sort (cost=96,518.93..97,044.81 rows=210,352 width=67) (actual time=1,711.636..1,723.836 rows=26,567 loops=1)

  • Sort Key: ad.data_source_type, ad.data_source_id, ad.indicator_id
  • Sort Method: external merge Disk: 15616kB
73. 1,006.927 1,006.927 ↓ 1.0 215,083 1

Seq Scan on aggregate_data ad (cost=0.00..69,290.24 rows=210,352 width=67) (actual time=2.393..1,006.927 rows=215,083 loops=1)

  • Filter: (report_period_start >= (now() - '9 mons'::interval))
  • Rows Removed by Filter: 1982546
74. 175.323 16,594.742 ↓ 3.1 3,085 1

Sort (cost=60.08..62.58 rows=1,000 width=44) (actual time=16,594.199..16,594.742 rows=3,085 loops=1)

  • Sort Key: dsl.type, dsl.id, dsl.indicator_id
  • Sort Method: external sort Disk: 9712kB
75. 16,419.419 16,419.419 ↓ 233.0 232,968 1

Function Scan on data_points_valid_list dsl (cost=0.25..10.25 rows=1,000 width=44) (actual time=16,355.487..16,419.419 rows=232,968 loops=1)

76. 0.958 1.936 ↑ 1.1 4,902 1

Hash (cost=97.96..97.96 rows=5,196 width=8) (actual time=1.936..1.936 rows=4,902 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 256kB
77. 0.978 0.978 ↑ 1.1 4,902 1

Seq Scan on organization_member cm_2 (cost=0.00..97.96 rows=5,196 width=8) (actual time=0.021..0.978 rows=4,902 loops=1)

78. 0.049 0.105 ↓ 1.1 206 1

Hash (cost=7.88..7.88 rows=188 width=27) (actual time=0.105..0.105 rows=206 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
79. 0.056 0.056 ↓ 1.1 206 1

Seq Scan on organization c (cost=0.00..7.88 rows=188 width=27) (actual time=0.008..0.056 rows=206 loops=1)

80. 0.047 0.146 ↓ 1.1 206 1

Hash (cost=7.88..7.88 rows=188 width=8) (actual time=0.146..0.146 rows=206 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
81. 0.099 0.099 ↓ 1.1 206 1

Seq Scan on organization o (cost=0.00..7.88 rows=188 width=8) (actual time=0.008..0.099 rows=206 loops=1)

82. 0.005 0.015 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=36) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on organization_type ot (cost=0.00..1.03 rows=3 width=36) (actual time=0.009..0.010 rows=3 loops=1)

Planning time : 9.972 ms
Execution time : 18,359.787 ms