explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PJXx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.384 5,122.864 ↑ 1.0 1 1

Aggregate (cost=33,951,294.30..33,951,294.31 rows=1 width=32) (actual time=5,122.863..5,122.864 rows=1 loops=1)

2. 0.016 5,119.480 ↑ 1.0 50 1

Limit (cost=33,951,290.54..33,951,290.67 rows=50 width=652) (actual time=5,119.451..5,119.480 rows=50 loops=1)

3. 0.388 5,119.464 ↑ 180.0 50 1

Sort (cost=33,951,290.54..33,951,313.04 rows=9,000 width=652) (actual time=5,119.451..5,119.464 rows=50 loops=1)

  • Sort Key: (clients.client_name) NULLS FIRST, a.start_month
  • Sort Method: top-N heapsort Memory: 38kB
4. 1.038 5,119.076 ↑ 43.5 207 1

WindowAgg (cost=56,193.41..33,950,991.57 rows=9,000 width=652) (actual time=5,118.931..5,119.076 rows=207 loops=1)

5. 1.035 5,118.038 ↑ 43.5 207 1

Nested Loop (cost=56,193.41..33,950,879.07 rows=9,000 width=277) (actual time=410.870..5,118.038 rows=207 loops=1)

6. 0.706 380.222 ↑ 43.5 207 1

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

  • Join Filter: (clients.created_date <= (a.start_month + '1 mon'::interval))
  • Rows Removed by Join Filter: 101
7. 0.032 0.032 ↑ 71.4 14 1

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

8. 0.165 379.484 ↑ 1.2 22 14

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

9. 0.014 379.319 ↑ 1.2 22 1

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

10. 80.115 379.305 ↑ 1.2 22 1

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

  • Group Key: c.client_id
11. 110.476 299.190 ↑ 1.0 339,651 1

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

  • Hash Cond: (ua.client_id = c.client_id)
12. 188.631 188.631 ↑ 1.0 339,651 1

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

13. 0.020 0.083 ↑ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.063 0.063 ↑ 1.0 27 1

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

  • Filter: (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 4,736.781 ↑ 1.0 1 207

Unique (cost=3,764.90..3,766.42 rows=1 width=277) (actual time=22.882..22.883 rows=1 loops=207)

16.          

Initplan (forUnique)

17. 44.091 1,741.698 ↑ 1.0 1 207

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

18. 1,697.607 1,697.607 ↓ 11.5 887 207

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

  • Index Cond: ((created >= $1) AND (created <= ($1 + '1 mon'::interval)))
  • Filter: (client_id = $0)
  • Rows Removed by Filter: 12788
19. 1.449 936.882 ↑ 1.0 1 207

Aggregate (cost=1,332.22..1,332.23 rows=1 width=32) (actual time=4.525..4.526 rows=1 loops=207)

20. 935.433 935.433 ↓ 4.0 4 207

Seq Scan on client_daily_storage (cost=0.00..1,332.21 rows=1 width=8) (actual time=3.052..4.519 rows=4 loops=207)

  • Filter: ((client = $0) AND (date_trunc('day'::text, created_on) >= $1) AND (date_trunc('day'::text, created_on) < ($1 + '1 mon'::interval)) AND (date_trunc('day'::text, created_on) = ((date_trunc('month'::text, created_on) + '1 mon'::interval) - '1 day'::interval)))
  • Rows Removed by Filter: 27129
21. 4.761 722.223 ↑ 1.0 1 207

Aggregate (cost=1,828.78..1,828.79 rows=1 width=8) (actual time=3.489..3.489 rows=1 loops=207)

22. 3.231 717.462 ↓ 5.0 5 207

Nested Loop (cost=0.29..1,828.77 rows=1 width=32) (actual time=2.551..3.466 rows=5 loops=207)

23. 621.207 621.207 ↑ 1.4 112 207

Seq Scan on user_activity_status uas (cost=0.00..904.90 rows=156 width=80) (actual time=0.660..3.001 rows=112 loops=207)

  • Filter: ((status <> '4eb4379c-18fa-458f-96e9-1dd203a7bc43'::uuid) AND (client = $0))
  • Rows Removed by Filter: 15661
24. 93.024 93.024 ↓ 0.0 0 23,256

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

  • Index Cond: (user_registration_id = uas.user_registration)
  • Filter: ((started_on >= $1) AND (client_id = $0) AND (uas.registration = registration_id) AND (uas.learning_activity = learning_activity_id) AND (uas.user_id = user_id) AND (started_on <= ($1 + '1 mon'::interval)))
  • Rows Removed by Filter: 2
25. 0.828 302.841 ↑ 1.0 1 207

Aggregate (cost=317.66..317.67 rows=1 width=8) (actual time=1.462..1.463 rows=1 loops=207)

26. 7.328 302.013 ↓ 4.0 4 207

Nested Loop Semi Join (cost=0.28..317.66 rows=1 width=0) (actual time=1.265..1.459 rows=4 loops=207)

27. 267.651 267.651 ↓ 5.0 65 207

Seq Scan on registration r (cost=0.00..221.78 rows=13 width=16) (actual time=0.775..1.293 rows=65 loops=207)

  • Filter: ((activated_on >= $1) AND (activated_on <= ($1 + '1 mon'::interval)))
  • Rows Removed by Filter: 2494
28. 27.034 27.034 ↓ 0.0 0 13,517

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

  • Index Cond: (id = r.learning_activity_id)
  • Filter: (client_id = $0)
  • Rows Removed by Filter: 1
29. 4,735.953 4,736.160 ↑ 1.0 1 207

Group (cost=0.00..1.52 rows=1 width=277) (actual time=22.879..22.880 rows=1 loops=207)

  • Group Key: clients.client_id, a.start_month
30. 0.207 0.207 ↑ 1.0 1 207

Result (cost=0.00..0.01 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=207)

Planning time : 2.207 ms
Execution time : 5,123.088 ms