explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DiPu : Optimization for: Optimization for: plan #wJWI; plan #UFij

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.401 3,026.981 ↑ 1.0 1 1

Aggregate (cost=28,693,777.44..28,693,777.45 rows=1 width=32) (actual time=3,026.980..3,026.981 rows=1 loops=1)

  • Output: jsonb_pretty(jsonb_agg(jsonb_build_object('total_count', (count(*) OVER (?)), 'client_uuid', COALESCE(((c.client_id))::text, ''::text), 'date_range', COALESCE((to_char(a.start_month, 'Month YYYY'::text)), ''::text), 'month', (date_trunc('month'::text, a.start_month)), 'client_name', COALESCE((c.client_name), ''::text), 'client_id', COALESCE(((c.accounting_id))::text, ''::text), 'org_code', COALESCE((c.org_code), ''::text), 'notes', COALESCE((c.notes), ''::text), 'active_users', COALESCE((($2))::text, '0'::text), 'ecommerce_enabled', COALESCE(($3), 'No'::text), 'enabled_authors', COALESCE((($5))::text, '0'::text), 'file_storage', COALESCE((($6))::text, '0'::text), 'learning_activities_started', COALESCE((($11))::text, '0'::text), 'registrations_activated', COALESCE((($13))::text, '0'::text), 'archived_users', COALESCE((($17))::text, '0'::text), 'disabled_users', COALESCE((($21))::text, '0'::text), 'enabled_users', COALESCE((($26))::text, '0'::text), 'self_signup_enabled', COALESCE(((c.self_signup_enabled))::text, '0'::text), 'enabled_disabled_users', COALESCE((((($31 + $35))::integer))::text, '0'::text))))
  • Buffers: shared hit=866693 read=62003 written=8
2. 0.023 3,023.580 ↑ 1.0 50 1

Limit (cost=28,693,773.69..28,693,773.81 rows=50 width=422) (actual time=3,023.546..3,023.580 rows=50 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::bigint, NULL::text, NULL::boolean, NULL::text, NULL::timestamp with time zone, (c.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (c.client_name), (c.accounting_id), (c.org_code), (c.self_signup_enabled), (c.notes), ($2), ($3), ($5), ($6), ($11), ($13), ($17), ($21), ($26), ((($31 + $35))::integer), (count(*) OVER (?))
  • Buffers: shared hit=866693 read=62003 written=8
3. 0.298 3,023.557 ↑ 180.0 50 1

Sort (cost=28,693,773.69..28,693,796.19 rows=9,000 width=422) (actual time=3,023.546..3,023.557 rows=50 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::bigint, NULL::text, NULL::boolean, NULL::text, NULL::timestamp with time zone, (c.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (c.client_name), (c.accounting_id), (c.org_code), (c.self_signup_enabled), (c.notes), ($2), ($3), ($5), ($6), ($11), ($13), ($17), ($21), ($26), ((($31 + $35))::integer), (count(*) OVER (?))
  • Sort Key: (c.client_name) NULLS FIRST, a.start_month
  • Sort Method: top-N heapsort Memory: 38kB
  • Buffers: shared hit=866693 read=62003 written=8
4. 0.841 3,023.259 ↑ 43.5 207 1

WindowAgg (cost=55,612.06..28,693,474.71 rows=9,000 width=422) (actual time=3,023.148..3,023.259 rows=207 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::bigint, NULL::text, NULL::boolean, NULL::text, NULL::timestamp with time zone, (c.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (c.client_name), (c.accounting_id), (c.org_code), (c.self_signup_enabled), (c.notes), ($2), ($3), ($5), ($6), ($11), ($13), ($17), ($21), ($26), ((($31 + $35))::integer), count(*) OVER (?)
  • Buffers: shared hit=866693 read=62003 written=8
5. 1.100 3,022.418 ↑ 43.5 207 1

Nested Loop (cost=55,612.06..28,693,362.21 rows=9,000 width=277) (actual time=405.237..3,022.418 rows=207 loops=1)

  • Output: a.start_month, (c.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (c.client_name), (c.accounting_id), (c.org_code), (c.self_signup_enabled), (c.notes), ($2), ($3), ($5), ($6), ($11), ($13), ($17), ($21), ($26), ((($31 + $35))::integer)
  • Buffers: shared hit=866693 read=62003 written=8
6. 0.534 386.001 ↑ 43.5 207 1

Nested Loop (cost=52,429.82..52,912.93 rows=9,000 width=85) (actual time=385.341..386.001 rows=207 loops=1)

  • Output: a.start_month, c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes
  • Join Filter: ((min(ua.created)) <= (a.start_month + '1 mon'::interval))
  • Rows Removed by Join Filter: 101
  • Buffers: shared hit=8437 read=34226
7. 0.033 0.033 ↑ 71.4 14 1

Function Scan on pg_catalog.generate_series a (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.022..0.033 rows=14 loops=1)

  • Output: a.start_month
  • Function Call: generate_series(date_trunc('month'::text, (('now'::cstring)::date - '1 year 1 mon'::interval)), date_trunc('month'::text, (('now'::cstring)::date)::timestamp without time zone), '1 mon'::interval)
8. 0.130 385.434 ↑ 1.2 22 14

Materialize (cost=52,429.80..52,430.48 rows=27 width=85) (actual time=27.521..27.531 rows=22 loops=14)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes, (min(ua.created))
  • Buffers: shared hit=8437 read=34226
9. 85.163 385.304 ↑ 1.2 22 1

HashAggregate (cost=52,429.80..52,430.07 rows=27 width=85) (actual time=385.285..385.304 rows=22 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes, min(ua.created)
  • Group Key: c.client_id
  • Buffers: shared hit=8437 read=34226
10. 115.642 300.141 ↑ 1.0 339,662 1

Hash Join (cost=4.52..50,731.49 rows=339,662 width=85) (actual time=0.089..300.141 rows=339,662 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes, ua.created
  • Hash Cond: (ua.client_id = c.client_id)
  • Buffers: shared hit=8437 read=34226
11. 184.432 184.432 ↑ 1.0 339,662 1

Seq Scan on app.user_activity ua (cost=0.00..46,056.62 rows=339,662 width=24) (actual time=0.014..184.432 rows=339,662 loops=1)

  • Output: ua.created, ua.client_id
  • Buffers: shared hit=8434 read=34226
12. 0.016 0.067 ↑ 1.0 27 1

Hash (cost=4.18..4.18 rows=27 width=77) (actual time=0.067..0.067 rows=27 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=3
13. 0.051 0.051 ↑ 1.0 27 1

Seq Scan on app.clients c (cost=0.00..4.18 rows=27 width=77) (actual time=0.007..0.051 rows=27 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes
  • Filter: (c.client_id = ANY ('{7873be06-3eca-4f73-a96d-b5be0d96bdfe,75c2d1de-224a-4a3f-bed1-a54d4a2a324e,95f413ff-99ad-4ce1-a1a3-b392ce774481,98113065-ff4e-455c-a82f-0581334092f6,f00a05c8-bd1b-4931-852d-a1e573eb1ece,089bc8bd-7c2e-491e-abd1-d37bff7a13ef,a3d54fc7-ced9-41ce-b168-4cb157462ba8,c5030b7b-28ab-4502-9bd4-529e2983749b,a3c33495-166b-4026-9b87-ecd0922a2e6e,6a773398-3be7-4982-a657-7962ac388f72,600c971d-7350-458c-a1b5-a4e47cb956b6,445a738a-67e5-48a8-a377-d01191a10739,26d87efe-3e9a-4971-ad69-f16a3f574081,0406f9a9-06b1-4b81-8c5f-e227af451c0d,de39d946-5dc9-44c5-9801-4dc7e9cd73d8,d8c59f19-c327-4efc-96c8-7fb65e53ed34,8dacf1d1-d9ec-4bc7-a8ed-816c06e77a3b,653618c9-da4a-4101-b335-673f77c94171,7c894818-f4dd-4042-9aa1-00f5664fef2e,e602c0c8-b4cc-45d7-a358-692c43106717,1360fff6-7d9d-4bab-8347-c73af3180301,d7392023-013e-4862-b745-5541af5bfa4c,af078153-a98b-4359-9bd1-ae8e660fe8ae,723b2c93-c777-4671-9af6-9e2f454ed280,d8a97e9d-ea44-473e-be5b-650737196744,21a9e5c1-e2d4-4f63-976b-a6fc34d66bdd,b9e64cf5-ef3c-4f49-bd79-75f7d69ea3b4}'::uuid[]))
  • Buffers: shared hit=3
14. 0.000 2,635.317 ↑ 1.0 1 207

Unique (cost=3,182.23..3,182.25 rows=1 width=277) (actual time=12.731..12.731 rows=1 loops=207)

  • Output: (c.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (c.client_name), (c.accounting_id), (c.org_code), (c.self_signup_enabled), (c.notes), ($2), ($3), ($5), ($6), ($11), ($13), ($17), ($21), ($26), ((($31 + $35))::integer), (a.start_month)
  • Buffers: shared hit=858256 read=27777 written=8
15.          

Initplan (forUnique)

16. 51.750 1,680.840 ↑ 1.0 1 207

Aggregate (cost=286.20..286.21 rows=1 width=8) (actual time=8.120..8.120 rows=1 loops=207)

  • Output: count(DISTINCT ua_1.user_id)
  • Buffers: shared hit=582802 read=26915 written=8
17. 1,629.090 1,629.090 ↓ 11.5 887 207

Index Scan using user_activity_created_index on app.user_activity ua_1 (cost=0.42..286.01 rows=77 width=16) (actual time=4.984..7.870 rows=887 loops=207)

  • Output: ua_1.user_id
  • Index Cond: ((ua_1.created >= $1) AND (ua_1.created <= ($1 + '1 mon'::interval)))
  • Filter: (ua_1.client_id = $0)
  • Rows Removed by Filter: 12789
  • Buffers: shared hit=582802 read=26915 written=8
18. 1.035 1.035 ↓ 0.0 0 207

Seq Scan on app.client_ecommerce (cost=0.00..13.40 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=207)

  • Output: CASE WHEN (((client_ecommerce.disabled_on >= $1) AND (client_ecommerce.disabled_on <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((client_ecommerce.disabled_on IS NULL) AND (client_ecommerce.enabled_on IS NOT NULL)) OR ((client_ecommerce.enabled_on >= $1) AND (client_ecommerce.enabled_on > client_ecommerce.disabled_on)) OR (client_ecommerce.disabled_on IS NULL) OR (client_ecommerce.enabled_on > client_ecommerce.disabled_on)) THEN 'Yes'::text WHEN (((client_ecommerce.disabled_on <= $1) AND (client_ecommerce.enabled_on < client_ecommerce.disabled_on)) OR (client_ecommerce.enabled_on > (($1 + '1 mon'::interval) - '00:00:00.001'::interval)) OR (client_ecommerce.enabled_on IS NULL)) THEN 'No'::text ELSE 'No'::text END
  • Filter: (client_ecommerce.client = $0)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=207
19. 1.242 2.070 ↑ 1.0 1 207

Aggregate (cost=40.01..40.02 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=207)

  • Output: (count(DISTINCT ae.user_id))::integer
  • Buffers: shared hit=207
20. 0.828 0.828 ↓ 0.0 0 207

Seq Scan on app.user_author_enabled ae (cost=16.53..40.01 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=207)

  • Output: ae.id, ae.client, ae.user_id, ae.user_name, ae.enabled_by, ae.enabled_name, ae.enabled_on
  • Filter: ((ae.client = $0) AND (((ae.enabled_on >= $1) AND (ae.enabled_on < (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((ae.enabled_on < $1) AND (NOT (hashed SubPlan 3)))))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=207
21.          

SubPlan (forSeq Scan)

22. 0.000 0.000 ↓ 0.0 0 32

Seq Scan on app.user_author_disabled ad (cost=0.00..16.12 rows=163 width=16) (actual time=0.000..0.000 rows=0 loops=32)

  • Output: ad.user_id
  • Filter: (ad.disabled_on < $1)
23. 1.242 380.880 ↑ 1.0 1 207

Aggregate (cost=381.41..381.42 rows=1 width=32) (actual time=1.840..1.840 rows=1 loops=207)

  • Output: sum(client_daily_storage.data_usage)
  • Buffers: shared hit=52877 read=102
24. 350.658 379.638 ↓ 4.0 4 207

Bitmap Heap Scan on app.client_daily_storage (cost=28.14..381.40 rows=1 width=8) (actual time=1.185..1.834 rows=4 loops=207)

  • Output: client_daily_storage.id, client_daily_storage.client, client_daily_storage.portal, client_daily_storage.data_usage, client_daily_storage.created_on
  • Recheck Cond: (client_daily_storage.client = $0)
  • Filter: ((date_trunc('day'::text, client_daily_storage.created_on) >= $1) AND (date_trunc('day'::text, client_daily_storage.created_on) < ($1 + '1 mon'::interval)) AND (date_trunc('day'::text, client_daily_storage.created_on) = ((date_trunc('month'::text, client_daily_storage.created_on) + '1 mon'::interval) - '1 day'::interval)))
  • Rows Removed by Filter: 1611
  • Heap Blocks: exact=51293
  • Buffers: shared hit=52877 read=102
25. 28.980 28.980 ↓ 1.5 1,614 207

Bitmap Index Scan on fki_client_daily_storage_client_idx (cost=0.00..28.14 rows=1,047 width=0) (actual time=0.140..0.140 rows=1,614 loops=207)

  • Index Cond: (client_daily_storage.client = $0)
  • Buffers: shared hit=1584 read=102
26. 3.519 196.236 ↑ 1.0 1 207

Aggregate (cost=1,723.30..1,723.31 rows=1 width=8) (actual time=0.948..0.948 rows=1 loops=207)

  • Output: count(DISTINCT ROW(ul.user_registration_id, ul.learning_activity_id))
  • Buffers: shared hit=111070 read=618
27. 2.610 192.717 ↓ 5.0 5 207

Nested Loop (cost=45.76..1,723.30 rows=1 width=32) (actual time=0.515..0.931 rows=5 loops=207)

  • Output: ul.user_registration_id, ul.learning_activity_id
  • Buffers: shared hit=111070 read=618
28. 78.867 97.083 ↑ 1.5 112 207

Bitmap Heap Scan on app.user_activity_status uas (cost=45.47..755.77 rows=167 width=80) (actual time=0.106..0.469 rows=112 loops=207)

  • Output: uas.user_registration, uas.registration, uas.learning_activity, uas.user_id, uas.client
  • Recheck Cond: (uas.client = $0)
  • Filter: (uas.status <> '4eb4379c-18fa-458f-96e9-1dd203a7bc43'::uuid)
  • Rows Removed by Filter: 926
  • Heap Blocks: exact=24438
  • Buffers: shared hit=25392 read=280
29. 18.216 18.216 ↑ 1.7 1,038 207

Bitmap Index Scan on fki_user_activity_status_client_idx (cost=0.00..45.43 rows=1,753 width=0) (actual time=0.088..0.088 rows=1,038 loops=207)

  • Index Cond: (uas.client = $0)
  • Buffers: shared hit=1172 read=62
30. 93.024 93.024 ↓ 0.0 0 23,256

Index Scan using user_registration_learning_index on app.user_learning ul (cost=0.29..5.78 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=23,256)

  • Output: ul.user_registration_id, ul.learning_activity_id, ul.registration_id, ul.user_id, ul.client_id
  • Index Cond: (ul.user_registration_id = uas.user_registration)
  • Filter: ((ul.started_on >= $1) AND (ul.client_id = $0) AND (uas.registration = ul.registration_id) AND (uas.learning_activity = ul.learning_activity_id) AND (uas.user_id = ul.user_id) AND (ul.started_on <= ($1 + '1 mon'::interval)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=85678 read=338
31. 0.621 42.435 ↑ 1.0 1 207

Aggregate (cost=136.84..136.85 rows=1 width=8) (actual time=0.205..0.205 rows=1 loops=207)

  • Output: count(1)
  • Buffers: shared hit=47900 read=64
32. 3.602 41.814 ↓ 4.0 4 207

Nested Loop Semi Join (cost=0.56..136.84 rows=1 width=0) (actual time=0.167..0.202 rows=4 loops=207)

  • Buffers: shared hit=47900 read=64
33. 11.178 11.178 ↓ 5.0 65 207

Index Scan using registration_activated_on_idx on app.registration r (cost=0.28..40.96 rows=13 width=16) (actual time=0.023..0.054 rows=65 loops=207)

  • Output: r.learning_activity_id
  • Index Cond: ((r.activated_on >= $1) AND (r.activated_on <= ($1 + '1 mon'::interval)))
  • Buffers: shared hit=7382 read=31
34. 27.034 27.034 ↓ 0.0 0 13,517

Index Scan using learning_activity_pkey on app.learning_activity (cost=0.28..7.37 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=13,517)

  • Output: learning_activity.id
  • Index Cond: (learning_activity.id = r.learning_activity_id)
  • Filter: (learning_activity.client_id = $0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=40518 read=33
35. 1.242 26.082 ↑ 1.0 1 207

Aggregate (cost=115.81..115.82 rows=1 width=4) (actual time=0.126..0.126 rows=1 loops=207)

  • Output: (count(DISTINCT u.user_id))::integer
  • Buffers: shared hit=4155 read=8
36. 0.417 24.840 ↓ 0.0 0 207

Nested Loop (cost=9.60..115.79 rows=7 width=16) (actual time=0.116..0.120 rows=0 loops=207)

  • Output: u.user_id
  • Buffers: shared hit=4155 read=8
37. 6.417 24.219 ↓ 0.0 0 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.114..0.117 rows=0 loops=207)

  • Output: u.user_id
  • Hash Cond: (u.user_id = usa.user_id)
  • Filter: (CASE WHEN (usa.user_id IS NULL) THEN (u.acct_status_id = '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid) ELSE (usa.status_change = '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid) END AND (((CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa.changed_on END >= $1) AND (CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa.changed_on END <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa.changed_on END < $1) AND (NOT (SubPlan 8)))))
  • Rows Removed by Filter: 98
  • Buffers: shared hit=3935 read=6
38. 10.764 13.455 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u (cost=4.78..71.02 rows=65 width=32) (actual time=0.017..0.065 rows=98 loops=207)

  • Output: u.user_id, u.acct_status_id
  • Recheck Cond: (u.client_id = $0)
  • Heap Blocks: exact=3040
  • Buffers: shared hit=3521 read=6
39. 2.691 2.691 ↓ 1.5 99 207

Bitmap Index Scan on users_client_id_idx (cost=0.00..4.76 rows=65 width=0) (actual time=0.013..0.013 rows=99 loops=207)

  • Index Cond: (u.client_id = $0)
  • Buffers: shared hit=487
40. 0.414 4.347 ↑ 34.0 2 207

Hash (cost=3.69..3.69 rows=68 width=40) (actual time=0.021..0.021 rows=2 loops=207)

  • Output: usa.user_id, usa.status_change, usa.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
41. 3.933 3.933 ↑ 34.0 2 207

Seq Scan on app.user_status_audit usa (cost=0.00..3.69 rows=68 width=40) (actual time=0.017..0.019 rows=2 loops=207)

  • Output: usa.user_id, usa.status_change, usa.changed_on
  • Filter: (usa.client = $0)
  • Rows Removed by Filter: 133
  • Buffers: shared hit=414
42.          

SubPlan (forHash Left Join)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on app.user_status_audit (cost=0.00..4.70 rows=1 width=16) (never executed)

  • Output: user_status_audit.user_id
  • Filter: ((user_status_audit.status_change = ANY ('{d9257be2-9ae1-47dd-a97b-04b1b35be4ba,45f7f874-ac15-404f-82d9-9385e4adcfbf}'::uuid[])) AND (user_status_audit.changed_on < $1) AND (CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa.changed_on END < user_status_audit.changed_on) AND (user_status_audit.client = $0))
44. 0.204 0.204 ↑ 1.0 1 68

Index Only Scan using user_portal_access_user_id_idx on app.user_portal_access upa (cost=0.28..5.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=68)

  • Output: upa.user_id
  • Index Cond: (upa.user_id = u.user_id)
  • Heap Fetches: 95
  • Buffers: shared hit=220 read=2
45. 1.449 33.534 ↑ 1.0 1 207

Aggregate (cost=115.81..115.82 rows=1 width=4) (actual time=0.162..0.162 rows=1 loops=207)

  • Output: (count(DISTINCT u_1.user_id))::integer
  • Buffers: shared hit=6612
46. 0.688 32.085 ↑ 1.4 5 207

Nested Loop (cost=9.60..115.79 rows=7 width=16) (actual time=0.136..0.155 rows=5 loops=207)

  • Output: u_1.user_id
  • Buffers: shared hit=6612
47. 15.111 29.187 ↑ 1.2 5 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.134..0.141 rows=5 loops=207)

  • Output: u_1.user_id
  • Hash Cond: (u_1.user_id = usa_1.user_id)
  • Filter: (CASE WHEN (usa_1.user_id IS NULL) THEN (u_1.acct_status_id = '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid) ELSE (usa_1.status_change = '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid) END AND (((CASE WHEN (usa_1.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_1.changed_on END >= $1) AND (CASE WHEN (usa_1.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_1.changed_on END <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((CASE WHEN (usa_1.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_1.changed_on END < $1) AND (NOT (SubPlan 10)))))
  • Rows Removed by Filter: 93
  • Buffers: shared hit=3941
48. 7.659 10.143 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u_1 (cost=4.78..71.02 rows=65 width=32) (actual time=0.015..0.049 rows=98 loops=207)

  • Output: u_1.user_id, u_1.acct_status_id
  • Recheck Cond: (u_1.client_id = $0)
  • Heap Blocks: exact=3040
  • Buffers: shared hit=3527
49. 2.484 2.484 ↓ 1.5 99 207

Bitmap Index Scan on users_client_id_idx (cost=0.00..4.76 rows=65 width=0) (actual time=0.012..0.012 rows=99 loops=207)

  • Index Cond: (u_1.client_id = $0)
  • Buffers: shared hit=487
50. 0.414 3.933 ↑ 34.0 2 207

Hash (cost=3.69..3.69 rows=68 width=40) (actual time=0.019..0.019 rows=2 loops=207)

  • Output: usa_1.user_id, usa_1.status_change, usa_1.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
51. 3.519 3.519 ↑ 34.0 2 207

Seq Scan on app.user_status_audit usa_1 (cost=0.00..3.69 rows=68 width=40) (actual time=0.016..0.017 rows=2 loops=207)

  • Output: usa_1.user_id, usa_1.status_change, usa_1.changed_on
  • Filter: (usa_1.client = $0)
  • Rows Removed by Filter: 133
  • Buffers: shared hit=414
52.          

SubPlan (forHash Left Join)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on app.user_status_audit user_status_audit_1 (cost=0.00..4.70 rows=1 width=16) (never executed)

  • Output: user_status_audit_1.user_id
  • Filter: ((user_status_audit_1.status_change = ANY ('{d9257be2-9ae1-47dd-a97b-04b1b35be4ba,720fe7d4-3c17-4757-a13a-ddadd40d880a}'::uuid[])) AND (user_status_audit_1.changed_on < $1) AND (CASE WHEN (usa_1.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_1.changed_on END < user_status_audit_1.changed_on) AND (user_status_audit_1.client = $0))
54. 2.210 2.210 ↑ 1.0 1 1,105

Index Only Scan using user_portal_access_user_id_idx on app.user_portal_access upa_1 (cost=0.28..5.46 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,105)

  • Output: upa_1.user_id
  • Index Cond: (upa_1.user_id = u_1.user_id)
  • Heap Fetches: 392
  • Buffers: shared hit=2671
55. 3.519 116.748 ↑ 1.0 1 207

Aggregate (cost=126.77..126.78 rows=1 width=4) (actual time=0.564..0.564 rows=1 loops=207)

  • Output: (count(DISTINCT u_2.user_id))::integer
  • Buffers: shared hit=22872 read=70
56. 3.201 113.229 ↓ 2.7 24 207

Nested Loop (cost=9.60..126.75 rows=9 width=16) (actual time=0.084..0.547 rows=24 loops=207)

  • Output: u_2.user_id
  • Buffers: shared hit=22872 read=70
57. 11.002 98.739 ↓ 2.2 18 207

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.076..0.477 rows=18 loops=207)

  • Output: u_2.user_id
  • Hash Cond: (u_2.user_id = usa_2.user_id)
  • Filter: (CASE WHEN (usa_2.user_id IS NULL) THEN (u_2.acct_status_id = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid) ELSE ((usa_2.status_change = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid) OR (usa_2.status_previous = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid)) END AND (((CASE WHEN (usa_2.changed_on IS NULL) THEN u_2.created_on ELSE usa_2.changed_on END >= $1) AND (CASE WHEN (usa_2.changed_on IS NULL) THEN u_2.created_on ELSE usa_2.changed_on END <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((CASE WHEN (usa_2.changed_on IS NULL) THEN u_2.created_on ELSE usa_2.changed_on END < $1) AND (NOT (SubPlan 12)))))
  • Rows Removed by Filter: 80
  • Buffers: shared hit=11673
58. 8.280 10.557 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u_2 (cost=4.78..71.02 rows=65 width=40) (actual time=0.014..0.051 rows=98 loops=207)

  • Output: u_2.user_id, u_2.acct_status_id, u_2.created_on
  • Recheck Cond: (u_2.client_id = $0)
  • Heap Blocks: exact=3040
  • Buffers: shared hit=3527
59. 2.277 2.277 ↓ 1.5 99 207

Bitmap Index Scan on users_client_id_idx (cost=0.00..4.76 rows=65 width=0) (actual time=0.011..0.011 rows=99 loops=207)

  • Index Cond: (u_2.client_id = $0)
  • Buffers: shared hit=487
60. 0.207 3.726 ↑ 34.0 2 207

Hash (cost=3.69..3.69 rows=68 width=56) (actual time=0.018..0.018 rows=2 loops=207)

  • Output: usa_2.user_id, usa_2.status_change, usa_2.status_previous, usa_2.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
61. 3.519 3.519 ↑ 34.0 2 207

Seq Scan on app.user_status_audit usa_2 (cost=0.00..3.69 rows=68 width=56) (actual time=0.015..0.017 rows=2 loops=207)

  • Output: usa_2.user_id, usa_2.status_change, usa_2.status_previous, usa_2.changed_on
  • Filter: (usa_2.client = $0)
  • Rows Removed by Filter: 133
  • Buffers: shared hit=414
62.          

SubPlan (forHash Left Join)

63. 73.454 73.454 ↓ 0.0 0 3,866

Seq Scan on app.user_status_audit user_status_audit_2 (cost=0.00..4.70 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=3,866)

  • Output: user_status_audit_2.user_id
  • Filter: ((user_status_audit_2.status_change = ANY ('{45f7f874-ac15-404f-82d9-9385e4adcfbf,720fe7d4-3c17-4757-a13a-ddadd40d880a}'::uuid[])) AND (user_status_audit_2.changed_on < $1) AND (CASE WHEN (usa_2.changed_on IS NULL) THEN u_2.created_on ELSE usa_2.changed_on END < user_status_audit_2.changed_on) AND (user_status_audit_2.client = $0))
  • Rows Removed by Filter: 135
  • Buffers: shared hit=7732
64. 11.289 11.289 ↑ 1.0 1 3,763

Index Only Scan using user_portal_access_user_id_idx on app.user_portal_access upa_2 (cost=0.28..5.46 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,763)

  • Output: upa_2.user_id
  • Index Cond: (upa_2.user_id = u_2.user_id)
  • Heap Fetches: 4013
  • Buffers: shared hit=11199 read=70
65. 3.312 114.885 ↑ 1.0 1 207

Aggregate (cost=126.77..126.78 rows=1 width=8) (actual time=0.555..0.555 rows=1 loops=207)

  • Output: count(DISTINCT u_3.user_id)
  • Buffers: shared hit=22942
66. 4.273 111.573 ↓ 2.7 24 207

Nested Loop (cost=9.60..126.75 rows=9 width=16) (actual time=0.082..0.539 rows=24 loops=207)

  • Output: u_3.user_id
  • Buffers: shared hit=22942
67. 11.830 99.774 ↓ 2.2 18 207

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.076..0.482 rows=18 loops=207)

  • Output: u_3.user_id
  • Hash Cond: (u_3.user_id = usa_3.user_id)
  • Filter: (CASE WHEN (usa_3.user_id IS NULL) THEN (u_3.acct_status_id = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid) ELSE ((usa_3.status_change = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid) OR (usa_3.status_previous = 'd9257be2-9ae1-47dd-a97b-04b1b35be4ba'::uuid)) END AND (((CASE WHEN (usa_3.changed_on IS NULL) THEN u_3.created_on ELSE usa_3.changed_on END >= $1) AND (CASE WHEN (usa_3.changed_on IS NULL) THEN u_3.created_on ELSE usa_3.changed_on END <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((CASE WHEN (usa_3.changed_on IS NULL) THEN u_3.created_on ELSE usa_3.changed_on END < $1) AND (NOT (SubPlan 14)))))
  • Rows Removed by Filter: 80
  • Buffers: shared hit=11673
68. 8.280 10.557 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u_3 (cost=4.78..71.02 rows=65 width=40) (actual time=0.014..0.051 rows=98 loops=207)

  • Output: u_3.user_id, u_3.acct_status_id, u_3.created_on
  • Recheck Cond: (u_3.client_id = $0)
  • Heap Blocks: exact=3040
  • Buffers: shared hit=3527
69. 2.277 2.277 ↓ 1.5 99 207

Bitmap Index Scan on users_client_id_idx (cost=0.00..4.76 rows=65 width=0) (actual time=0.011..0.011 rows=99 loops=207)

  • Index Cond: (u_3.client_id = $0)
  • Buffers: shared hit=487
70. 0.207 3.933 ↑ 34.0 2 207

Hash (cost=3.69..3.69 rows=68 width=56) (actual time=0.019..0.019 rows=2 loops=207)

  • Output: usa_3.user_id, usa_3.status_change, usa_3.status_previous, usa_3.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
71. 3.726 3.726 ↑ 34.0 2 207

Seq Scan on app.user_status_audit usa_3 (cost=0.00..3.69 rows=68 width=56) (actual time=0.017..0.018 rows=2 loops=207)

  • Output: usa_3.user_id, usa_3.status_change, usa_3.status_previous, usa_3.changed_on
  • Filter: (usa_3.client = $0)
  • Rows Removed by Filter: 133
  • Buffers: shared hit=414
72.          

SubPlan (forHash Left Join)

73. 73.454 73.454 ↓ 0.0 0 3,866

Seq Scan on app.user_status_audit user_status_audit_3 (cost=0.00..4.70 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=3,866)

  • Output: user_status_audit_3.user_id
  • Filter: ((user_status_audit_3.status_change = ANY ('{45f7f874-ac15-404f-82d9-9385e4adcfbf,720fe7d4-3c17-4757-a13a-ddadd40d880a}'::uuid[])) AND (user_status_audit_3.changed_on < $1) AND (CASE WHEN (usa_3.changed_on IS NULL) THEN u_3.created_on ELSE usa_3.changed_on END < user_status_audit_3.changed_on) AND (user_status_audit_3.client = $0))
  • Rows Removed by Filter: 135
  • Buffers: shared hit=7732
74. 7.526 7.526 ↑ 1.0 1 3,763

Index Only Scan using user_portal_access_user_id_idx on app.user_portal_access upa_3 (cost=0.28..5.46 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=3,763)

  • Output: upa_3.user_id
  • Index Cond: (upa_3.user_id = u_3.user_id)
  • Heap Fetches: 4013
  • Buffers: shared hit=11269
75. 1.449 33.741 ↑ 1.0 1 207

Aggregate (cost=115.81..115.82 rows=1 width=8) (actual time=0.163..0.163 rows=1 loops=207)

  • Output: count(DISTINCT u_4.user_id)
  • Buffers: shared hit=6612
76. 0.688 32.292 ↑ 1.4 5 207

Nested Loop (cost=9.60..115.79 rows=7 width=16) (actual time=0.136..0.156 rows=5 loops=207)

  • Output: u_4.user_id
  • Buffers: shared hit=6612
77. 15.525 29.394 ↑ 1.2 5 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.134..0.142 rows=5 loops=207)

  • Output: u_4.user_id
  • Hash Cond: (u_4.user_id = usa_4.user_id)
  • Filter: (CASE WHEN (usa_4.user_id IS NULL) THEN (u_4.acct_status_id = '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid) ELSE (usa_4.status_change = '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid) END AND (((CASE WHEN (usa_4.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_4.changed_on END >= $1) AND (CASE WHEN (usa_4.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_4.changed_on END <= (($1 + '1 mon'::interval) - '00:00:00.001'::interval))) OR ((CASE WHEN (usa_4.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_4.changed_on END < $1) AND (NOT (SubPlan 16)))))
  • Rows Removed by Filter: 93
  • Buffers: shared hit=3941
78. 7.659 10.143 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u_4 (cost=4.78..71.02 rows=65 width=32) (actual time=0.015..0.049 rows=98 loops=207)

  • Output: u_4.user_id, u_4.acct_status_id
  • Recheck Cond: (u_4.client_id = $0)
  • Heap Blocks: exact=3040
  • Buffers: shared hit=3527
79. 2.484 2.484 ↓ 1.5 99 207

Bitmap Index Scan on users_client_id_idx (cost=0.00..4.76 rows=65 width=0) (actual time=0.012..0.012 rows=99 loops=207)

  • Index Cond: (u_4.client_id = $0)
  • Buffers: shared hit=487
80. 0.207 3.726 ↑ 34.0 2 207

Hash (cost=3.69..3.69 rows=68 width=40) (actual time=0.018..0.018 rows=2 loops=207)

  • Output: usa_4.user_id, usa_4.status_change, usa_4.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
81. 3.519 3.519 ↑ 34.0 2 207

Seq Scan on app.user_status_audit usa_4 (cost=0.00..3.69 rows=68 width=40) (actual time=0.015..0.017 rows=2 loops=207)

  • Output: usa_4.user_id, usa_4.status_change, usa_4.changed_on
  • Filter: (usa_4.client = $0)
  • Rows Removed by Filter: 133
  • Buffers: shared hit=414
82.          

SubPlan (forHash Left Join)

83. 0.000 0.000 ↓ 0.0 0

Seq Scan on app.user_status_audit user_status_audit_4 (cost=0.00..4.70 rows=1 width=16) (never executed)

  • Output: user_status_audit_4.user_id
  • Filter: ((user_status_audit_4.status_change = ANY ('{d9257be2-9ae1-47dd-a97b-04b1b35be4ba,720fe7d4-3c17-4757-a13a-ddadd40d880a}'::uuid[])) AND (user_status_audit_4.changed_on < $1) AND (CASE WHEN (usa_4.changed_on IS NULL) THEN '2019-06-12 10:54:09.667975-04'::timestamp with time zone ELSE usa_4.changed_on END < user_status_audit_4.changed_on) AND (user_status_audit_4.client = $0))
84. 2.210 2.210 ↑ 1.0 1 1,105

Index Only Scan using user_portal_access_user_id_idx on app.user_portal_access upa_4 (cost=0.28..5.46 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,105)

  • Output: upa_4.user_id
  • Index Cond: (upa_4.user_id = u_4.user_id)
  • Heap Fetches: 392
  • Buffers: shared hit=2671
85. 2,634.903 2,634.903 ↑ 1.0 1 207

Result (cost=0.00..0.02 rows=1 width=277) (actual time=12.729..12.729 rows=1 loops=207)

  • Output: c.client_id, date_trunc('month'::text, a.start_month), to_char(a.start_month, 'Month YYYY'::text), c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes, $2, $3, $5, $6, $11, $13, $17, $21, $26, (($31 + $35))::integer, a.start_month
  • Buffers: shared hit=858256 read=27777 written=8
Planning time : 6.229 ms
Execution time : 3,027.493 ms