explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yMrX : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #PJXx; plan #eQGA; plan #kvB7; plan #Yax6G; plan #BSkI; plan #vGEQ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.415 3,822.088 ↑ 1.0 1 1

Aggregate (cost=22,807,036.73..22,807,036.74 rows=1 width=32) (actual time=3,822.088..3,822.088 rows=1 loops=1)

  • Output: jsonb_pretty(jsonb_agg(jsonb_build_object('total_count', (count(*) OVER (?)), 'client_uuid', COALESCE(((clients.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((clients.client_name), ''::text), 'client_id', COALESCE(((clients.accounting_id))::text, ''::text), 'org_code', COALESCE((clients.org_code), ''::text), 'notes', COALESCE((clients.notes), ''::text), 'active_users', COALESCE((($2))::text, '0'::text), 'ecommerce_enabled', COALESCE((ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), 'No'::text), 'enabled_authors', COALESCE(((enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)))::text, '0'::text), 'file_storage', COALESCE((($3))::text, '0'::text), 'learning_activities_started', COALESCE((($8))::text, '0'::text), 'registrations_activated', COALESCE((($10))::text, '0'::text), 'archived_users', COALESCE(((non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)))::text, '0'::text), 'disabled_users', COALESCE(((non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)))::text, '0'::text), 'enabled_users', COALESCE(((enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)))::text, '0'::text), 'self_signup_enabled', COALESCE(((clients.self_signup_enabled))::text, '0'::text), 'enabled_disabled_users', COALESCE(((enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)))::text, '0'::text))))
2. 0.017 3,818.673 ↑ 1.0 50 1

Limit (cost=22,807,032.98..22,807,033.10 rows=50 width=652) (actual time=3,818.641..3,818.673 rows=50 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::text, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::bigint, NULL::text, NULL::timestamp with time zone, NULL::interval, NULL::integer, NULL::boolean, NULL::text, NULL::boolean, NULL::boolean, NULL::timestamp with time zone, (clients.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (clients.client_name), (clients.accounting_id), (clients.org_code), (clients.self_signup_enabled), (clients.notes), ($2), (ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (count(*) OVER (?))
3. 0.325 3,818.656 ↑ 180.0 50 1

Sort (cost=22,807,032.98..22,807,055.48 rows=9,000 width=652) (actual time=3,818.640..3,818.656 rows=50 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::text, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::bigint, NULL::text, NULL::timestamp with time zone, NULL::interval, NULL::integer, NULL::boolean, NULL::text, NULL::boolean, NULL::boolean, NULL::timestamp with time zone, (clients.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (clients.client_name), (clients.accounting_id), (clients.org_code), (clients.self_signup_enabled), (clients.notes), ($2), (ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (count(*) OVER (?))
  • Sort Key: (clients.client_name) NULLS FIRST, a.start_month
  • Sort Method: top-N heapsort Memory: 38kB
4. 0.996 3,818.331 ↑ 43.5 207 1

WindowAgg (cost=54,955.16..22,806,734.00 rows=9,000 width=652) (actual time=3,818.192..3,818.331 rows=207 loops=1)

  • Output: a.start_month, NULL::timestamp without time zone, NULL::uuid, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::text, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, NULL::bigint, NULL::text, NULL::timestamp with time zone, NULL::interval, NULL::integer, NULL::boolean, NULL::text, NULL::boolean, NULL::boolean, NULL::timestamp with time zone, (clients.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (clients.client_name), (clients.accounting_id), (clients.org_code), (clients.self_signup_enabled), (clients.notes), ($2), (ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), count(*) OVER (?)
5. 0.906 3,817.335 ↑ 43.5 207 1

Nested Loop (cost=54,955.16..22,806,621.50 rows=9,000 width=277) (actual time=517.382..3,817.335 rows=207 loops=1)

  • Output: a.start_month, (clients.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (clients.client_name), (clients.accounting_id), (clients.org_code), (clients.self_signup_enabled), (clients.notes), ($2), (ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid))
6. 0.534 494.493 ↑ 43.5 207 1

Nested Loop (cost=52,428.51..52,911.62 rows=9,000 width=85) (actual time=493.807..494.493 rows=207 loops=1)

  • Output: a.start_month, clients.client_id, clients.client_name, clients.accounting_id, clients.org_code, clients.self_signup_enabled, clients.notes
  • Join Filter: (clients.created_date <= (a.start_month + '1 mon'::interval))
  • Rows Removed by Join Filter: 101
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.150 493.920 ↑ 1.2 22 14

Materialize (cost=52,428.49..52,429.16 rows=27 width=85) (actual time=35.267..35.280 rows=22 loops=14)

  • Output: clients.client_id, clients.client_name, clients.accounting_id, clients.org_code, clients.self_signup_enabled, clients.notes, clients.created_date
9. 0.008 493.770 ↑ 1.2 22 1

Subquery Scan on clients (cost=52,428.49..52,429.03 rows=27 width=85) (actual time=493.735..493.770 rows=22 loops=1)

  • Output: clients.client_id, clients.client_name, clients.accounting_id, clients.org_code, clients.self_signup_enabled, clients.notes, clients.created_date
10. 107.907 493.762 ↑ 1.2 22 1

HashAggregate (cost=52,428.49..52,428.76 rows=27 width=315) (actual time=493.734..493.762 rows=22 loops=1)

  • Output: c.client_id, c.client_name, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, c.org_code, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::text, NULL::text, NULL::jsonb, NULL::boolean, NULL::boolean, NULL::boolean, c.accounting_id, NULL::text, NULL::timestamp with time zone, NULL::interval, NULL::integer, NULL::boolean, c.notes, NULL::boolean, c.self_signup_enabled, min(ua.created)
  • Group Key: c.client_id
11. 143.597 385.855 ↑ 1.0 339,651 1

Hash Join (cost=4.52..50,730.23 rows=339,651 width=85) (actual time=0.170..385.855 rows=339,651 loops=1)

  • Output: c.client_id, c.client_name, c.org_code, c.accounting_id, c.notes, c.self_signup_enabled, ua.created
  • Hash Cond: (ua.client_id = c.client_id)
12. 242.121 242.121 ↑ 1.0 339,651 1

Seq Scan on app.user_activity ua (cost=0.00..46,055.51 rows=339,651 width=24) (actual time=0.009..242.121 rows=339,651 loops=1)

  • Output: ua.created, ua.client_id
13. 0.017 0.137 ↑ 1.0 27 1

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

  • Output: c.client_id, c.client_name, c.org_code, c.accounting_id, c.notes, c.self_signup_enabled
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.120 0.120 ↑ 1.0 27 1

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

  • Output: c.client_id, c.client_name, c.org_code, c.accounting_id, c.notes, c.self_signup_enabled
  • 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[]))
15. 0.000 3,321.936 ↑ 1.0 1 207

Unique (cost=2,526.65..2,528.17 rows=1 width=277) (actual time=16.048..16.048 rows=1 loops=207)

  • Output: (clients.client_id), (date_trunc('month'::text, a.start_month)), (to_char(a.start_month, 'Month YYYY'::text)), (clients.client_name), (clients.accounting_id), (clients.org_code), (clients.self_signup_enabled), (clients.notes), ($2), (ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid)), (a.start_month)
16.          

Initplan (for Unique)

17. 45.333 1,698.849 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ua_1.user_id)
18. 1,653.516 1,653.516 ↓ 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.029..7.988 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: 12788
19. 1.449 376.119 ↑ 1.0 1 207

Aggregate (cost=380.27..380.28 rows=1 width=32) (actual time=1.816..1.817 rows=1 loops=207)

  • Output: sum(client_daily_storage.data_usage)
20. 346.311 374.670 ↓ 4.0 4 207

Bitmap Heap Scan on app.client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.176..1.810 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: 1606
  • Heap Blocks: exact=51072
21. 28.359 28.359 ↓ 1.5 1,609 207

Bitmap Index Scan on fki_client_daily_storage_client_idx (cost=0.00..28.12 rows=1,044 width=0) (actual time=0.137..0.137 rows=1,609 loops=207)

  • Index Cond: (client_daily_storage.client = $0)
22. 3.726 189.819 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ROW(ul.user_registration_id, ul.learning_activity_id))
23. 0.000 186.093 ↓ 5.0 5 207

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

  • Output: ul.user_registration_id, ul.learning_activity_id
24. 77.625 95.427 ↑ 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.104..0.461 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
25. 17.802 17.802 ↑ 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.086..0.086 rows=1,038 loops=207)

  • Index Cond: (uas.client = $0)
26. 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
27. 0.414 39.951 ↑ 1.0 1 207

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

  • Output: count(1)
28. 1.946 39.537 ↓ 4.0 4 207

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

29. 10.557 10.557 ↓ 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.051 rows=65 loops=207)

  • Output: r.learning_activity_id
  • Index Cond: ((r.activated_on >= $1) AND (r.activated_on <= ($1 + '1 mon'::interval)))
30. 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
31. 3,321.522 3,321.522 ↑ 1.0 1 207

Result (cost=0.00..1.51 rows=1 width=277) (actual time=16.046..16.046 rows=1 loops=207)

  • Output: clients.client_id, date_trunc('month'::text, a.start_month), to_char(a.start_month, 'Month YYYY'::text), clients.client_name, clients.accounting_id, clients.org_code, clients.self_signup_enabled, clients.notes, $2, ecommerce_enabled_by_month(a.start_month, clients.client_id, 'en'::text), enabled_authors_by_month(a.start_month, clients.client_id, NULL::uuid), $3, $8, $10, non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid), non_enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid), enabled_users_by_month(a.start_month, clients.client_id, NULL::uuid), enabled_plus_disabled_users_by_month(a.start_month, clients.client_id, NULL::uuid), a.start_month
Planning time : 3.228 ms
Execution time : 3,822.435 ms