explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UFij : Optimization for: plan #wJWI

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.422 2,996.250 ↑ 1.0 1 1

Aggregate (cost=28,693,777.35..28,693,777.37 rows=1 width=32) (actual time=2,996.250..2,996.250 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=865137 read=63550 written=1
2. 0.023 2,992.828 ↑ 1.0 50 1

Limit (cost=28,693,773.60..28,693,773.73 rows=50 width=422) (actual time=2,992.799..2,992.828 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=865137 read=63550 written=1
3. 0.304 2,992.805 ↑ 180.0 50 1

Sort (cost=28,693,773.60..28,693,796.10 rows=9,000 width=422) (actual time=2,992.798..2,992.805 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=865137 read=63550 written=1
4. 0.830 2,992.501 ↑ 43.5 207 1

WindowAgg (cost=55,611.97..28,693,474.63 rows=9,000 width=422) (actual time=2,992.407..2,992.501 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=865137 read=63550 written=1
5. 1.098 2,991.671 ↑ 43.5 207 1

Nested Loop (cost=55,611.97..28,693,362.13 rows=9,000 width=277) (actual time=419.807..2,991.671 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=865137 read=63550 written=1
6. 0.502 398.519 ↑ 43.5 207 1

Nested Loop (cost=52,429.74..52,912.84 rows=9,000 width=85) (actual time=397.896..398.519 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=8864 read=33799
7. 0.039 0.039 ↑ 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.031..0.039 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.125 397.978 ↑ 1.2 22 14

Materialize (cost=52,429.71..52,430.39 rows=27 width=85) (actual time=28.417..28.427 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=8864 read=33799
9. 88.971 397.853 ↑ 1.2 22 1

HashAggregate (cost=52,429.71..52,429.98 rows=27 width=85) (actual time=397.827..397.853 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=8864 read=33799
10. 112.896 308.882 ↓ 1.0 339,662 1

Hash Join (cost=4.52..50,731.42 rows=339,659 width=85) (actual time=0.102..308.882 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=8864 read=33799
11. 195.918 195.918 ↓ 1.0 339,662 1

Seq Scan on app.user_activity ua (cost=0.00..46,056.59 rows=339,659 width=24) (actual time=0.009..195.918 rows=339,662 loops=1)

  • Output: ua.created, ua.client_id
  • Buffers: shared hit=8861 read=33799
12. 0.014 0.068 ↑ 1.0 27 1

Hash (cost=4.18..4.18 rows=27 width=77) (actual time=0.067..0.068 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.054 0.054 ↑ 1.0 27 1

Seq Scan on app.clients c (cost=0.00..4.18 rows=27 width=77) (actual time=0.009..0.054 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,592.054 ↑ 1.0 1 207

Unique (cost=3,182.23..3,182.25 rows=1 width=277) (actual time=12.522..12.522 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=856273 read=29751 written=1
15.          

Initplan (forUnique)

16. 49.680 1,641.096 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ua_1.user_id)
  • Buffers: shared hit=581778 read=27939 written=1
17. 1,591.416 1,591.416 ↓ 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.892..7.688 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=581778 read=27939 written=1
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=206 read=1
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=206 read=1
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.004..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=206 read=1
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.052 ↑ 1.0 1 207

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

  • Output: sum(client_daily_storage.data_usage)
  • Buffers: shared hit=52561 read=409
24. 349.209 378.810 ↓ 4.0 4 207

Bitmap Heap Scan on app.client_daily_storage (cost=28.14..381.40 rows=1 width=8) (actual time=1.183..1.830 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=52561 read=409
25. 29.601 29.601 ↓ 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.143..0.143 rows=1,614 loops=207)

  • Index Cond: (client_daily_storage.client = $0)
  • Buffers: shared hit=1578 read=99
26. 3.726 197.271 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ROW(ul.user_registration_id, ul.learning_activity_id))
  • Buffers: shared hit=110656 read=1032
27. 3.852 193.545 ↓ 5.0 5 207

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

  • Output: ul.user_registration_id, ul.learning_activity_id
  • Buffers: shared hit=110656 read=1032
28. 78.453 96.669 ↑ 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.467 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=25234 read=438
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=1174 read=60
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=85422 read=594
31. 0.414 40.572 ↑ 1.0 1 207

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

  • Output: count(1)
  • Buffers: shared hit=47825 read=139
32. 2.360 40.158 ↓ 4.0 4 207

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

  • Buffers: shared hit=47825 read=139
33. 10.764 10.764 ↓ 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.020..0.052 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=7340 read=73
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=40485 read=66
35. 1.242 25.875 ↑ 1.0 1 207

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

  • Output: (count(DISTINCT u.user_id))::integer
  • Buffers: shared hit=4104 read=59
36. 0.349 24.633 ↓ 0.0 0 207

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

  • Output: u.user_id
  • Buffers: shared hit=4104 read=59
37. 6.417 24.012 ↓ 0.0 0 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.112..0.116 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:24:35.912302-04'::timestamp with time zone ELSE usa.changed_on END >= $1) AND (CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:24:35.912302-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:24:35.912302-04'::timestamp with time zone ELSE usa.changed_on END < $1) AND (NOT (SubPlan 8)))))
  • Rows Removed by Filter: 98
  • Buffers: shared hit=3884 read=57
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=3472 read=55
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=480 read=7
40. 0.414 4.140 ↑ 34.0 2 207

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

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

Seq Scan on app.user_status_audit usa (cost=0.00..3.69 rows=68 width=40) (actual time=0.016..0.018 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=412 read=2
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:24:35.912302-04'::timestamp with time zone ELSE usa.changed_on END < user_status_audit.changed_on) AND (user_status_audit.client = $0))
44. 0.272 0.272 ↑ 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.004 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.120 ↑ 1.0 1 207

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

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

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

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

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.132..0.140 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:24:35.912302-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:24:35.912302-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:24:35.912302-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 9.936 ↓ 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.048 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.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_1.client_id = $0)
  • Buffers: shared hit=487
50. 0.414 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_1.user_id, usa_1.status_change, usa_1.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
51. 3.312 3.312 ↑ 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.015..0.016 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:24:35.912302-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 117.162 ↑ 1.0 1 207

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

  • Output: (count(DISTINCT u_2.user_id))::integer
  • Buffers: shared hit=22771 read=171
56. 3.822 113.643 ↓ 2.7 24 207

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

  • Output: u_2.user_id
  • Buffers: shared hit=22771 read=171
57. 10.795 98.532 ↓ 2.2 18 207

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.074..0.476 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.414 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.312 3.312 ↑ 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.016 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=11098 read=171
65. 3.312 114.057 ↑ 1.0 1 207

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

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

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

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

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.074..0.478 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.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_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.519 3.519 ↑ 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.015..0.017 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.327 ↑ 1.0 1 207

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

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

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

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

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.133..0.141 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:24:35.912302-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:24:35.912302-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:24:35.912302-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 9.936 ↓ 1.5 98 207

Bitmap Heap Scan on app.users u_4 (cost=4.78..71.02 rows=65 width=32) (actual time=0.014..0.048 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.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_4.client_id = $0)
  • Buffers: shared hit=487
80. 0.414 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.312 3.312 ↑ 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.016 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:24:35.912302-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,591.640 2,591.640 ↑ 1.0 1 207

Result (cost=0.00..0.02 rows=1 width=277) (actual time=12.520..12.520 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=856273 read=29751 written=1
Planning time : 6.118 ms
Execution time : 2,997.068 ms