explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.407 3,676.831 ↑ 1.0 1 1

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

2. 0.013 3,673.424 ↑ 1.0 50 1

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

3. 0.323 3,673.411 ↑ 180.0 50 1

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

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

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

5. 0.922 3,672.119 ↑ 43.5 207 1

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

6. 0.580 368.926 ↑ 43.5 207 1

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

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

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

8. 0.159 368.298 ↑ 1.2 22 14

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

9. 0.005 368.139 ↑ 1.2 22 1

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

10. 80.300 368.134 ↑ 1.2 22 1

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

  • Group Key: c.client_id
11. 110.354 287.834 ↑ 1.0 339,651 1

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

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

13. 0.022 0.127 ↑ 1.0 27 1

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

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

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

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

16.          

Initplan (forUnique)

17. 45.126 1,679.184 ↑ 1.0 1 207

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

18. 1,634.058 1,634.058 ↓ 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.054..7.894 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 378.189 ↑ 1.0 1 207

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

20. 347.760 376.740 ↓ 4.0 4 207

Bitmap Heap Scan on client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.179..1.820 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.980 28.980 ↓ 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.140..0.140 rows=1,609 loops=207)

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

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

23. 0.000 186.507 ↓ 5.0 5 207

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

24. 78.246 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.105..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.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.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.414 40.779 ↑ 1.0 1 207

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

28. 2.153 40.365 ↓ 4.0 4 207

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

29. 11.178 11.178 ↓ 5.0 65 207

Index Scan using registration_activated_on_idx on registration r (cost=0.28..40.96 rows=13 width=16) (actual time=0.021..0.054 rows=65 loops=207)

  • Index Cond: ((activated_on >= $1) AND (activated_on <= ($1 + '1 mon'::interval)))
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,301.857 3,301.857 ↑ 1.0 1 207

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

Planning time : 3.326 ms
Execution time : 3,677.292 ms