explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.381 3,674.178 ↑ 1.0 1 1

Aggregate (cost=22,807,081.73..22,807,081.74 rows=1 width=32) (actual time=3,674.178..3,674.178 rows=1 loops=1)

2. 0.011 3,670.797 ↑ 1.0 50 1

Limit (cost=22,807,077.98..22,807,078.10 rows=50 width=652) (actual time=3,670.773..3,670.797 rows=50 loops=1)

3. 0.337 3,670.786 ↑ 180.0 50 1

Sort (cost=22,807,077.98..22,807,100.48 rows=9,000 width=652) (actual time=3,670.772..3,670.786 rows=50 loops=1)

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

WindowAgg (cost=54,955.16..22,806,779.00 rows=9,000 width=652) (actual time=3,670.312..3,670.449 rows=207 loops=1)

5. 0.725 3,669.537 ↑ 43.5 207 1

Nested Loop (cost=54,955.16..22,806,666.50 rows=9,000 width=277) (actual time=435.793..3,669.537 rows=207 loops=1)

6. 0.591 412.702 ↑ 43.5 207 1

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

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

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

8. 0.117 412.076 ↑ 1.2 22 14

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

9. 0.009 411.959 ↑ 1.2 22 1

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

10. 91.023 411.950 ↑ 1.2 22 1

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

  • Group Key: c.client_id
11. 117.767 320.927 ↑ 1.0 339,651 1

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

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

13. 0.012 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)

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

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

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

16.          

Initplan (forUnique)

17. 45.126 1,649.376 ↑ 1.0 1 207

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

18. 1,604.250 1,604.250 ↓ 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.950..7.750 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.242 374.463 ↑ 1.0 1 207

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

20. 344.241 373.221 ↓ 4.0 4 207

Bitmap Heap Scan on client_daily_storage (cost=28.12..380.27 rows=1 width=8) (actual time=1.165..1.803 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 188.163 ↑ 1.0 1 207

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

23. 0.000 184.644 ↓ 5.0 5 207

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

24. 77.418 95.427 ↑ 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.461 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 39.537 ↑ 1.0 1 207

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

28. 1.739 39.123 ↓ 4.0 4 207

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

29. 10.350 10.350 ↓ 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.050 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,255.282 3,255.489 ↑ 1.0 1 207

Group (cost=0.00..1.52 rows=1 width=277) (actual time=15.727..15.727 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)