explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wJWI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.436 2,992.154 ↑ 1.0 1 1

Aggregate (cost=28,661,026.43..28,661,026.44 rows=1 width=32) (actual time=2,992.153..2,992.154 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=861541 read=72154 written=5
2. 0.023 2,988.718 ↑ 1.0 50 1

Limit (cost=28,661,022.67..28,661,022.80 rows=50 width=422) (actual time=2,988.683..2,988.718 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=861541 read=72154 written=5
3. 0.319 2,988.695 ↑ 180.0 50 1

Sort (cost=28,661,022.67..28,661,045.17 rows=9,000 width=422) (actual time=2,988.683..2,988.695 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=861541 read=72154 written=5
4. 0.869 2,988.376 ↑ 43.5 207 1

WindowAgg (cost=3,208.74..28,660,723.70 rows=9,000 width=422) (actual time=2,988.266..2,988.376 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=861541 read=72154 written=5
5. 1.175 2,987.507 ↑ 43.5 207 1

Nested Loop (cost=3,208.74..28,660,611.20 rows=9,000 width=277) (actual time=24.883..2,987.507 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=861541 read=72154 written=5
6. 0.552 414.150 ↑ 43.5 207 1

Nested Loop (cost=26.50..20,161.92 rows=9,000 width=85) (actual time=3.424..414.150 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=4851 read=42820 written=5
7. 0.038 0.038 ↑ 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.026..0.038 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.128 413.560 ↑ 1.2 22 14

Materialize (cost=26.48..19,679.46 rows=27 width=85) (actual time=0.242..29.540 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=4851 read=42820 written=5
9. 50.145 413.432 ↑ 1.2 22 1

GroupAggregate (cost=26.48..19,679.06 rows=27 width=85) (actual time=3.379..413.432 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=4851 read=42820 written=5
10. 71.780 363.287 ↓ 2.0 339,662 1

Nested Loop (cost=26.48..18,829.64 rows=169,830 width=85) (actual time=0.156..363.287 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
  • Join Filter: (c.client_id = ua.client_id)
  • Buffers: shared hit=4851 read=42820 written=5
11. 0.071 0.258 ↓ 1.9 27 1

Merge Join (cost=26.06..27.46 rows=14 width=81) (actual time=0.137..0.258 rows=27 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes, i.i
  • Merge Cond: (c.client_id = (('{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[])[i.i]))
  • Buffers: shared hit=3
12. 0.047 0.097 ↑ 1.0 27 1

Sort (cost=3.91..3.98 rows=27 width=77) (actual time=0.079..0.097 rows=27 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes
  • Sort Key: c.client_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=3
13. 0.050 0.050 ↑ 1.0 27 1

Seq Scan on app.clients c (cost=0.00..3.27 rows=27 width=77) (actual time=0.009..0.050 rows=27 loops=1)

  • Output: c.client_id, c.client_name, c.accounting_id, c.org_code, c.self_signup_enabled, c.notes
  • Buffers: shared hit=3
14. 0.052 0.090 ↑ 7.4 27 1

Sort (cost=22.15..22.65 rows=200 width=4) (actual time=0.054..0.090 rows=27 loops=1)

  • Output: i.i, (('{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[])[i.i])
  • Sort Key: (('{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[])[i.i])
  • Sort Method: quicksort Memory: 27kB
15. 0.014 0.038 ↑ 7.4 27 1

HashAggregate (cost=12.50..14.50 rows=200 width=4) (actual time=0.034..0.038 rows=27 loops=1)

  • Output: i.i, (('{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[])[i.i])
  • Group Key: ('{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[])[i.i]
16. 0.024 0.024 ↑ 37.0 27 1

Function Scan on pg_catalog.generate_series i (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.019..0.024 rows=27 loops=1)

  • Output: i.i, ('{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[])[i.i]
  • Function Call: generate_series(1, 27)
17. 291.249 291.249 ↑ 1.2 12,580 27

Index Scan using fki_user_activity_client_id_idx on app.user_activity ua (cost=0.42..1,150.03 rows=15,439 width=24) (actual time=0.011..10.787 rows=12,580 loops=27)

  • Output: ua.created, ua.client_id
  • Index Cond: (ua.client_id = ('{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[])[i.i])
  • Buffers: shared hit=4848 read=42820 written=5
18. 0.000 2,572.182 ↑ 1.0 1 207

Unique (cost=3,182.23..3,182.25 rows=1 width=277) (actual time=12.426..12.426 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=856690 read=29334
19.          

Initplan (for Unique)

20. 48.438 1,631.160 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ua_1.user_id)
  • Buffers: shared hit=582721 read=26996
21. 1,582.722 1,582.722 ↓ 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=5.015..7.646 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=582721 read=26996
22. 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
23. 1.242 1.863 ↑ 1.0 1 207

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

  • Output: (count(DISTINCT ae.user_id))::integer
  • Buffers: shared hit=207
24. 0.621 0.621 ↓ 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.003 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
25.          

SubPlan (for Seq Scan)

26. 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)
27. 1.242 372.600 ↑ 1.0 1 207

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

  • Output: sum(client_daily_storage.data_usage)
  • Buffers: shared hit=52821 read=149
28. 342.792 371.358 ↓ 4.0 4 207

Bitmap Heap Scan on app.client_daily_storage (cost=28.14..381.40 rows=1 width=8) (actual time=1.165..1.794 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=52821 read=149
29. 28.566 28.566 ↓ 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.138..0.138 rows=1,614 loops=207)

  • Index Cond: (client_daily_storage.client = $0)
  • Buffers: shared hit=1532 read=145
30. 3.726 197.892 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ROW(ul.user_registration_id, ul.learning_activity_id))
  • Buffers: shared hit=109915 read=1773
31. 2.196 194.166 ↓ 5.0 5 207

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

  • Output: ul.user_registration_id, ul.learning_activity_id
  • Buffers: shared hit=109915 read=1773
32. 80.730 98.946 ↑ 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.478 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=24869 read=803
33. 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=1145 read=89
34. 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=85046 read=970
35. 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=47788 read=176
36. 2.153 40.158 ↓ 4.0 4 207

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

  • Buffers: shared hit=47788 read=176
37. 10.971 10.971 ↓ 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.021..0.053 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=7303 read=110
38. 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
39. 1.242 24.840 ↑ 1.0 1 207

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

  • Output: (count(DISTINCT u.user_id))::integer
  • Buffers: shared hit=4127 read=36
40. 0.417 23.598 ↓ 0.0 0 207

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

  • Output: u.user_id
  • Buffers: shared hit=4127 read=36
41. 6.210 22.977 ↓ 0.0 0 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.108..0.111 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:20:07.392776-04'::timestamp with time zone ELSE usa.changed_on END >= $1) AND (CASE WHEN (usa.changed_on IS NULL) THEN '2019-06-12 10:20:07.392776-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:20:07.392776-04'::timestamp with time zone ELSE usa.changed_on END < $1) AND (NOT (SubPlan 8)))))
  • Rows Removed by Filter: 98
  • Buffers: shared hit=3906 read=35
42. 10.143 12.627 ↓ 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.061 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=3492 read=35
43. 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.client_id = $0)
  • Buffers: shared hit=481 read=6
44. 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=414
45. 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=414
46.          

SubPlan (for Hash Left Join)

47. 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:20:07.392776-04'::timestamp with time zone ELSE usa.changed_on END < user_status_audit.changed_on) AND (user_status_audit.client = $0))
48. 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=221 read=1
49. 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=6611 read=1
50. 0.688 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=6611 read=1
51. 15.111 28.773 ↑ 1.2 5 207

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.132..0.139 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:20:07.392776-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:20:07.392776-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:20:07.392776-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
52. 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
53. 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
54. 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_1.user_id, usa_1.status_change, usa_1.changed_on
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=414
55. 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.015..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
56.          

SubPlan (for Hash Left Join)

57. 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:20:07.392776-04'::timestamp with time zone ELSE usa_1.changed_on END < user_status_audit_1.changed_on) AND (user_status_audit_1.client = $0))
58. 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=2670 read=1
59. 3.312 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=22739 read=203
60. 4.029 113.436 ↓ 2.7 24 207

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

  • Output: u_2.user_id
  • Buffers: shared hit=22739 read=203
61. 10.381 98.118 ↓ 2.2 18 207

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.074..0.474 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
62. 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
63. 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
64. 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
65. 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
66.          

SubPlan (for Hash Left Join)

67. 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
68. 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=11066 read=203
69. 3.312 113.229 ↑ 1.0 1 207

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

  • Output: count(DISTINCT u_3.user_id)
  • Buffers: shared hit=22942
70. 4.066 109.917 ↓ 2.7 24 207

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

  • Output: u_3.user_id
  • Buffers: shared hit=22942
71. 10.588 98.325 ↓ 2.2 18 207

Hash Left Join (cost=9.32..82.95 rows=8 width=16) (actual time=0.074..0.475 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
72. 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
73. 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
74. 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
75. 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
76.          

SubPlan (for Hash Left Join)

77. 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
78. 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
79. 1.449 32.913 ↑ 1.0 1 207

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

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

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

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

Hash Left Join (cost=9.32..82.94 rows=6 width=16) (actual time=0.131..0.139 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:20:07.392776-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:20:07.392776-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:20:07.392776-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
82. 7.245 9.522 ↓ 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.046 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
83. 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
84. 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
85. 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
86.          

SubPlan (for Hash Left Join)

87. 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:20:07.392776-04'::timestamp with time zone ELSE usa_4.changed_on END < user_status_audit_4.changed_on) AND (user_status_audit_4.client = $0))
88. 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.001..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
89. 2,571.768 2,571.768 ↑ 1.0 1 207

Result (cost=0.00..0.02 rows=1 width=277) (actual time=12.424..12.424 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=856690 read=29334
Planning time : 6.435 ms
Execution time : 2,993.007 ms