explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eQGA : Optimization for: plan #PJXx

Settings

Optimization path:

Optimization(s) for this plan:

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

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

2. 0.015 3,895.063 ↑ 1.0 50 1

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

3. 0.343 3,895.048 ↑ 180.0 50 1

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

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

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

5. 0.838 3,893.779 ↑ 43.5 207 1

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

6. 0.593 374.562 ↑ 43.5 207 1

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

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

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

8. 0.122 373.926 ↑ 1.2 22 14

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

9. 0.008 373.804 ↑ 1.2 22 1

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

10. 80.479 373.796 ↑ 1.2 22 1

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

  • Group Key: c.client_id
11. 107.792 293.317 ↑ 1.0 339,651 1

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

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

13. 0.013 0.073 ↑ 1.0 27 1

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

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

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

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

16.          

Initplan (forUnique)

17. 46.989 1,668.627 ↑ 1.0 1 207

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

18. 1,621.638 1,621.638 ↓ 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.981..7.834 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 376.326 ↑ 1.0 1 207

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

20. 346.104 374.877 ↓ 4.0 4 207

Bitmap Heap Scan on client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.169..1.811 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. 28.773 28.773 ↓ 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.139..0.139 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.505..0.902 rows=5 loops=207)

24. 77.625 96.255 ↑ 1.5 112 207

Bitmap Heap Scan on user_activity_status uas (cost=45.47..755.77 rows=167 width=80) (actual time=0.107..0.465 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.630 18.630 ↑ 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.090..0.090 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.003..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 281.106 ↑ 1.0 1 207

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

28. 5.258 280.485 ↓ 4.0 4 207

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

29. 248.193 248.193 ↓ 5.0 65 207

Seq Scan on registration r (cost=0.00..221.78 rows=13 width=16) (actual time=0.710..1.199 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,517.758 3,517.965 ↑ 1.0 1 207

Group (cost=0.00..1.52 rows=1 width=277) (actual time=16.994..16.995 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.418 ms
Execution time : 3,898.699 ms