explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.382 3,686.372 ↑ 1.0 1 1

Aggregate (cost=22,807,036.73..22,807,036.74 rows=1 width=32) (actual time=3,686.372..3,686.372 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((ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), 'No'::text), 'enabled_authors', COALESCE(((enabled_authors_by_month(a.start_month, c.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, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)))::text, '0'::text), 'disabled_users', COALESCE(((non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)))::text, '0'::text), 'enabled_users', COALESCE(((enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)))::text, '0'::text), 'self_signup_enabled', COALESCE(((c.self_signup_enabled))::text, '0'::text), 'enabled_disabled_users', COALESCE(((enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid)))::text, '0'::text))))
2. 0.014 3,682.990 ↑ 1.0 50 1

Limit (cost=22,807,032.98..22,807,033.10 rows=50 width=422) (actual time=3,682.963..3,682.990 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), (ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (count(*) OVER (?))
3. 0.311 3,682.976 ↑ 180.0 50 1

Sort (cost=22,807,032.98..22,807,055.48 rows=9,000 width=422) (actual time=3,682.962..3,682.976 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), (ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (count(*) OVER (?))
  • Sort Key: (c.client_name) NULLS FIRST, a.start_month
  • Sort Method: top-N heapsort Memory: 38kB
4. 0.888 3,682.665 ↑ 43.5 207 1

WindowAgg (cost=54,955.16..22,806,734.00 rows=9,000 width=422) (actual time=3,682.556..3,682.665 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), (ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), count(*) OVER (?)
5. 0.855 3,681.777 ↑ 43.5 207 1

Nested Loop (cost=54,955.16..22,806,621.50 rows=9,000 width=277) (actual time=409.482..3,681.777 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), (ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid))
6. 0.545 386.724 ↑ 43.5 207 1

Nested Loop (cost=52,428.51..52,911.62 rows=9,000 width=85) (actual time=386.066..386.724 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
7. 0.031 0.031 ↑ 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.019..0.031 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.119 386.148 ↑ 1.2 22 14

Materialize (cost=52,428.49..52,429.16 rows=27 width=85) (actual time=27.573..27.582 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))
9. 85.147 386.029 ↑ 1.2 22 1

HashAggregate (cost=52,428.49..52,428.76 rows=27 width=85) (actual time=386.008..386.029 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
10. 107.513 300.882 ↑ 1.0 339,651 1

Hash Join (cost=4.52..50,730.23 rows=339,651 width=85) (actual time=0.126..300.882 rows=339,651 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)
11. 193.282 193.282 ↑ 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.007..193.282 rows=339,651 loops=1)

  • Output: ua.created, ua.client_id
12. 0.015 0.087 ↑ 1.0 27 1

Hash (cost=4.18..4.18 rows=27 width=77) (actual time=0.087..0.087 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
13. 0.072 0.072 ↑ 1.0 27 1

Seq Scan on app.clients c (cost=0.00..4.18 rows=27 width=77) (actual time=0.021..0.072 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[]))
14. 0.000 3,294.198 ↑ 1.0 1 207

Unique (cost=2,526.65..2,528.17 rows=1 width=277) (actual time=15.914..15.914 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), (ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text)), (enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid)), ($3), ($8), ($10), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid)), (non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid)), (enabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid)), (a.start_month)
15.          

Initplan (forUnique)

16. 45.126 1,670.076 ↑ 1.0 1 207

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

  • Output: count(DISTINCT ua_1.user_id)
17. 1,624.950 1,624.950 ↓ 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.992..7.850 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
18. 1.449 376.947 ↑ 1.0 1 207

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

  • Output: sum(client_daily_storage.data_usage)
19. 346.932 375.498 ↓ 4.0 4 207

Bitmap Heap Scan on app.client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.173..1.814 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
20. 28.566 28.566 ↓ 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.138..0.138 rows=1,609 loops=207)

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

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

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

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

  • Output: ul.user_registration_id, ul.learning_activity_id
23. 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
24. 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)
25. 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.003..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
26. 0.621 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)
27. 1.532 39.330 ↓ 4.0 4 207

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

28. 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.021..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)))
29. 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
30. 3,293.784 3,293.784 ↑ 1.0 1 207

Result (cost=0.00..1.51 rows=1 width=277) (actual time=15.912..15.912 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, ecommerce_enabled_by_month(a.start_month, c.client_id, 'en'::text), enabled_authors_by_month(a.start_month, c.client_id, NULL::uuid), $3, $8, $10, non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '720fe7d4-3c17-4757-a13a-ddadd40d880a'::uuid), non_enabled_users_by_month(a.start_month, c.client_id, NULL::uuid, '45f7f874-ac15-404f-82d9-9385e4adcfbf'::uuid), enabled_users_by_month(a.start_month, c.client_id, NULL::uuid), enabled_plus_disabled_users_by_month(a.start_month, c.client_id, NULL::uuid), a.start_month
Planning time : 2.180 ms
Execution time : 3,686.761 ms