explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kvB7 : Optimization for: Optimization for: plan #PJXx; plan #eQGA

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.435 3,922.235 ↑ 1.0 1 1

Aggregate (cost=24,434,487.30..24,434,487.31 rows=1 width=32) (actual time=3,922.235..3,922.235 rows=1 loops=1)

2. 0.012 3,918.800 ↑ 1.0 50 1

Limit (cost=24,434,483.54..24,434,483.67 rows=50 width=652) (actual time=3,918.778..3,918.800 rows=50 loops=1)

3. 0.410 3,918.788 ↑ 180.0 50 1

Sort (cost=24,434,483.54..24,434,506.04 rows=9,000 width=652) (actual time=3,918.777..3,918.788 rows=50 loops=1)

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

WindowAgg (cost=55,135.99..24,434,184.57 rows=9,000 width=652) (actual time=3,918.225..3,918.378 rows=207 loops=1)

5. 0.778 3,917.435 ↑ 43.5 207 1

Nested Loop (cost=55,135.99..24,434,072.07 rows=9,000 width=277) (actual time=404.204..3,917.435 rows=207 loops=1)

6. 0.556 379.855 ↑ 43.5 207 1

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

  • 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 generate_series a (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.030..0.039 rows=14 loops=1)

8. 0.122 379.260 ↑ 1.2 22 14

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

9. 0.006 379.138 ↑ 1.2 22 1

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

10. 85.173 379.132 ↑ 1.2 22 1

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

  • Group Key: c.client_id
11. 106.923 293.959 ↑ 1.0 339,651 1

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

  • Hash Cond: (ua.client_id = c.client_id)
12. 186.960 186.960 ↑ 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.009..186.960 rows=339,651 loops=1)

13. 0.011 0.076 ↑ 1.0 27 1

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

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

Seq Scan on clients c (cost=0.00..4.18 rows=27 width=77) (actual time=0.017..0.065 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 3,536.802 ↑ 1.0 1 207

Unique (cost=2,707.48..2,709.00 rows=1 width=277) (actual time=17.085..17.086 rows=1 loops=207)

16.          

Initplan (forUnique)

17. 47.403 1,671.939 ↑ 1.0 1 207

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

18. 1,624.536 1,624.536 ↓ 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=4.987..7.848 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 387.297 ↑ 1.0 1 207

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

20. 356.247 385.848 ↓ 4.0 4 207

Bitmap Heap Scan on client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.190..1.864 rows=4 loops=207)

  • Recheck Cond: (client = $0)
  • Filter: ((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: 1606
  • Heap Blocks: exact=51072
21. 29.601 29.601 ↓ 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.143..0.143 rows=1,609 loops=207)

  • Index Cond: (client = $0)
22. 3.519 190.233 ↑ 1.0 1 207

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

23. 0.000 186.714 ↓ 5.0 5 207

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

24. 78.039 96.048 ↑ 1.5 112 207

Bitmap Heap Scan on user_activity_status uas (cost=45.47..755.77 rows=167 width=80) (actual time=0.105..0.464 rows=112 loops=207)

  • Recheck Cond: (client = $0)
  • Filter: (status <> '4eb4379c-18fa-458f-96e9-1dd203a7bc43'::uuid)
  • Rows Removed by Filter: 926
  • Heap Blocks: exact=24438
25. 18.009 18.009 ↑ 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.087..0.087 rows=1,038 loops=207)

  • Index Cond: (client = $0)
26. 93.024 93.024 ↓ 0.0 0 23,256

Index Scan using user_registration_learning_index on user_learning ul (cost=0.29..5.78 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
27. 0.621 283.176 ↑ 1.0 1 207

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

28. 5.051 282.555 ↓ 4.0 4 207

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

29. 250.470 250.470 ↓ 5.0 65 207

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

  • Filter: ((activated_on >= $1) AND (activated_on <= ($1 + '1 mon'::interval)))
  • Rows Removed by Filter: 2494
30. 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
31. 3,536.181 3,536.388 ↑ 1.0 1 207

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

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

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

Planning time : 2.794 ms
Execution time : 3,922.507 ms