explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VcLb

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 61,599.013 ↑ 1.0 10 1

Limit (cost=326,132,661.3..326,132,663.12 rows=10 width=536) (actual time=61,598.346..61,599.013 rows=10 loops=1)

  • Buffers: shared hit=4704969, temp read=7615 written=7615
2.          

CTE adjustment

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0..0.01 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1)

4.          

CTE truncated_date

5. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0..0.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

6.          

CTE periods

7. 0.060 0.090 ↑ 32.3 31 1

Subquery Scan on dates (cost=0.02..35.03 rows=1,000 width=16) (actual time=0.025..0.09 rows=31 loops=1)

8. 0.027 0.030 ↑ 32.3 31 1

Result (cost=0.02..5.03 rows=1,000 width=8) (actual time=0.017..0.03 rows=31 loops=1)

9.          

Initplan (forResult)

10. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on truncated_date truncated_date (cost=0..0.02 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)

11.          

CTE player_ids

12. 17.335 182.851 ↓ 1.1 8,962 1

Hash Join (cost=20,142.05..24,673.76 rows=8,522 width=32) (actual time=149.884..182.851 rows=8,962 loops=1)

  • Buffers: shared hit=7930
13. 16.045 16.045 ↓ 1.0 83,734 1

Seq Scan on casino_players cp (cost=0..4,146.36 rows=80,036 width=32) (actual time=0.008..16.045 rows=83,734 loops=1)

  • Buffers: shared hit=3346
14. 2.864 149.471 ↑ 1.1 8,972 1

Hash (cost=20,018.48..20,018.48 rows=9,885 width=32) (actual time=149.471..149.471 rows=8,972 loops=1)

  • Buffers: shared hit=4584
15. 146.607 146.607 ↑ 1.1 8,972 1

Seq Scan on players p (cost=0..20,018.48 rows=9,885 width=32) (actual time=0.114..146.607 rows=8,972 loops=1)

  • Filter: (p.agent_id = ANY ('{e60e2b89-46dd-48ec-a17f-5c465f85cb4a,688c3ec7-278f-4d97-9c4f-e97b428aa0d8,a19f3ef1-9930-4a5d-8a67-3e59f35d17eb,aa73ad16-1055-4014-885f-07c7649f7bd4,a92b50ce-32d7-426b-959f-bc9e8805a258,78ff419b-d26a-4a16-b784-21501dcf6a1c,34f6f334-ab9a-4dc6-b027-178347e6da8e,10a58c6b-cef3-4e77-9ecb-b666deae089d,525415f8-7968-48da-a48e-d29189574665,3b175854-4202-48be-8edb-0d2fd5439b76,4795a366-c223-4491-b1d4-cf2338bce7ae,59305a56-9325-4ee6-ab1e-b1ea79bc8787,d305e834-5516-48d5-a76d-7d6c938ed031,6dcade5d-5e9b-47d9-9a1d-9d944e4cf287,6fd3922e-22ae-4cc4-8eae-bc8673e7c153,7890d9cb-447f-4f19-9318-5c326a931539,2083baea-d6ab-479d-a946-f5c9b5af002e,55a0737a-4122-4870-8e5d-2de421375969,87a4431d-c07d-40f7-a720-e62239875484,0405163b-94de-4b89-a6ed-46b1a562b404,d962c0f6-0614-49e9-a996-3adb22ff534d,59646186-ac7c-4425-bc5b-687107dfea8f,90bc90ed-9760-4655-982d-390cdf453d72,f899579c-ced7-420d-95b1-153642d488f4,2c62eda1-361a-4721-94e9-4b27e5475c98,1af33dcd-8668-401f-85b5-20ef7032b04e,f1879ceb-cefc-4cbf-ac8d-8668731f9cc9,bc796d83-d559-4360-9c7e-d67c24db8442,d3689009-aee3-4499-a69b-70ebe9d8ec4f,db575df2-0ab5-491c-a1a6-feb0890dfef7,cbb65f4f-91c3-4ef2-98d2-7f4ab4b62e08,426b4a2f-a603-4e05-8fee-3e8fa97a4262,1e1d934e-e371-43cb-909a-f8db48cfd724,2f4b99e9-31c8-4f6b-bbaa-b062cac037b2,675f20db-cc60-48b4-a1e5-48140286eb40,2abdc381-3401-4fea-8a00-2d7858ae30d2,415ed732-4c2a-4c63-b35a-815a2f29a484,529c9f81-f057-4c5b-8048-ad26da4fa1be,22721f6e-c1e0-4939-bb81-ff2aad6bf737,2f59773e-7d89-4a77-98cb-41ef67f0c5a0,c4a5b52c-3e9d-40a9-b278-bf3c398d4757,78def06f-75f1-425e-bdaf-443ae7763412,7a8b4534-cf45-4956-842d-9e8b8a14f8c5,3ef413b4-2073-4934-a962-aedd0bbd56dc,e4c5b88a-73fc-4c4a-8401-90a7665ccb09,962b8394-3c91-4ec7-bff0-3b64001744ae,3a775997-e470-48cb-84a8-8f2318db91d6,dcd325d0-feb9-4664-bb1b-0c5b9f2bc996,376007fa-c119-4e65-8bd0-da25fc0701cc,22fa4bd6-c2d4-48c5-9be5-a8e507f17f50,e63b7868-acab-4400-8f43-828c6ef9c8ab,05d08950-05a6-49e7-832f-943a7845ba67,f560f153-479f-468e-854c-deb25a4aa635,281c8007-d15e-4975-be1f-5d1419f1c76d,50a31441-7c0c-44d3-98a3-9bc482ece041,5d538c57-33fe-49d6-8fcb-21b7650b8671,411aea94-1a6a-4014-8f23-adeec2863c7b,59bdf899-1a44-4374-9a0c-6e01630f31ff,cfc26dad-b1eb-4383-acc4-0bfd3af7dfc4,b196abec-4923-404d-9c48-8cad87405248,67715a8c-4215-461d-a47f-2a17375f3f37,5dbbcfb8-4f4a-4d74-9b91-890c434b8e38,f1b261ad-11bc-4879-bc2a-1c146000b235,45fa1925-4c59-4d52-89b8-59eda913d7c0,b36c7b72-a2d2-44db-96e9-dd12b8bfcab9,279093e6-1a7d-4a17-b419-08ec141b2f50,9acf2e05-2d27-4e71-8adb-aa7aa543a8f2,fcb151dc-f01c-4215-a818-6e2a3fdf6e9d,2c610e1b-23fb-44bb-859a-b08a020c7359,b9aee346-f924-4775-812f-06163065c478,6d69a0f6-7d4b-4052-8c8e-5671b42edbaa,f0080701-8c7d-4328-95a7-764916fa6997,135aa295-9cf9-48a7-aabb-c188b55a63f5,d6ebc5af-7bf0-4d4a-a010-a5e897396977,3b2f622f-000f-4740-a506-23914288ccbf,8eb9f4a8-5cb7-4e70-8e10-b29860f18bf6,4c6f96d3-b937-433f-8750-3f1ecebc1b1e,6cdb5208-da64-4572-86da-0d3dd87ca00b,ecb45e3a-cd24-4773-9b43-b6de6702503e,46ab499a-d11e-422d-887f-6d6b5fbbbb90,daf5638e-cf1e-4bc7-b68a-ca61505dbcab,7ab1ef14-8f51-4649-b252-7392179be4ab,c34fad05-6582-482d-8033-8540dcf19f27,3026b747-078b-43fb-a588-bedde51d24c8,4c402d4a-bf94-4819-9d79-34f8d5b4caea,364ced6d-a3c3-4d05-980b-2712a8f548c0,43fc59f3-c664-4b44-9fb3-1515640e28d1,5cdf98d7-aa32-4879-b78b-85f72120d66f,87a934ce-0875-4147-a9b3-9d926b472875,039d01df-1d98-4baf-b1f3-286be1563a09,034dab31-29f9-497d-b31c-903b636faaea,aa15a739-e971-4d91-9249-f12ced7085c8,1cc6cd0b-0fae-4d39-ba2a-9e2dcd91ee3b,1a155113-c2c3-4aaa-94f6-3e7abd507bb1,02b246a6-9db1-4c7d-b1fb-143193f85dc7,20b21127-bcd6-4b0a-9aac-19a3f6cecc7c,3deec0e6-b33f-4cc3-a406-7786a0d15ebc,1b61a4e1-7c01-49c8-a178-15ec87e6b6cc,4ef39543-d413-4a2d-a39d-115636ccdb0a,d4b7c1aa-4d9a-4912-954c-a8aae072446a,d7efd8c3-edcd-493c-a034-be37516bbdbd,a43b509c-8295-40f9-b7ca-e50910a1cec0,707c9dbf-d5c9-413a-a1c2-5884dc733417,c4bafd81-de25-4655-82e2-17d1179174a7,a8923b24-717e-4d67-bcbc-b442f40c96d0,9e42d9cd-0831-4dee-b390-e758a092360a,bb115f26-18bf-4c91-ba02-9ff068e03162,2e1b7a9d-a471-4d33-89bd-a93ab1a8d978,c0fd09bf-3d63-46e1-a34b-d8b3293a0289,ed4e3cf1-9ec6-48c0-8680-73f8967887b2,9fabbcc4-58b4-47a9-a735-84ef6a095eba,185dd76d-898f-4fab-b2ea-ccfb63d195d7,acb7226c-cf9e-4bf8-9f7e-49b17515b7d6,b176f825-9a52-43fe-bab2-3d3399d7bd9e,42e4e72f-2346-4b3f-9ab0-25c907bad6ff,41b42473-941e-4133-a6b4-e8606482495f,a3311973-cfa6-4f64-aff7-7fde8d25cef4,d8ab5cd5-9669-4828-8a8b-e66c77cb574c,3b7f926d-accc-432a-a00f-8924301f7b11,5d4e4304-8c1b-49df-80ea-3633962b915c,77c3918a-3455-43e2-beb0-8d2a164cf0c0,e278719d-22ab-449a-8d89-aed1288a4ce4,d68b9ed0-2b50-4b45-8b9a-b01149fef7c5,6d6fc6fb-397e-41ba-9336-a538f497dc43,cc6ed0a0-6538-4426-8988-9286ad06e892}'::uuid[]))
  • Buffers: shared hit=4584
16.          

CTE game_history

17. 626.426 61,393.025 ↑ 321.0 1,246 1

Aggregate (cost=301,008,785.39..324,682,779.83 rows=400,000 width=276) (actual time=60,588.939..61,393.025 rows=1,246 loops=1)

  • Buffers: shared hit=4685448, temp read=7615 written=7615
18.          

Initplan (forAggregate)

19. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on adjustment adjustment (cost=0..0.02 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

20. 936.596 60,766.593 ↑ 816.3 681,999 1

Sort (cost=301,008,785.37..302,400,490.93 rows=556,682,222 width=130) (actual time=60,588.817..60,766.593 rows=681,999 loops=1)

  • Sort Key: gh.currency, p_1.agent_id, pp.start_date, pp.end_date
  • Sort Method: external merge Disk: 60904kB
  • Buffers: shared hit=4685448, temp read=7615 written=7615
21. 493.197 59,829.997 ↑ 816.3 681,999 1

Merge Join (cost=727.38..144,035,449.88 rows=556,682,222 width=130) (actual time=245.97..59,829.997 rows=681,999 loops=1)

  • Buffers: shared hit=4685448
22. 44,826.191 59,081.337 ↑ 115.1 3,158,084 1

Nested Loop (cost=0.58..134,775,357.56 rows=363,652,889 width=114) (actual time=0.213..59,081.337 rows=3,158,084 loops=1)

  • Buffers: shared hit=4677518
23. 1,924.517 7,893.070 ↑ 1.0 3,181,038 1

Nested Loop (cost=0.58..3,860,307.56 rows=3,272,876 width=106) (actual time=0.093..7,893.07 rows=3,181,038 loops=1)

  • Buffers: shared hit=4677518
24. 5,968.553 5,968.553 ↑ 1.0 3,181,038 1

Index Scan using ix_game_history_reports_casino_player_id on game_history_reports gh (cost=0.43..1,364,695.61 rows=3,272,876 width=50) (actual time=0.088..5,968.553 rows=3,181,038 loops=1)

  • Filter: ((gh.game_date >= '2019-04-01 00:00:00+08'::timestamp with time zone) AND (gh.game_date < '2019-05-01 00:00:00+08'::timestamp with time zone))
  • Buffers: shared hit=1496480
25. 0.000 0.000 ↓ 0.0 0 3,181,038

Index Scan using ux_side_bets_reports_ddc_casino_id_game_number_unique_ids on side_bets_reports sbr (cost=0.14..0.75 rows=1 width=72) (actual time=0..0 rows=0 loops=3,181,038)

  • Index Cond: (sbr.game_number = gh.game_number)
  • Buffers: shared hit=3181038
26. 6,362.076 6,362.076 ↑ 32.3 31 3,181,038

CTE Scan on periods pp (cost=0..20 rows=1,000 width=16) (actual time=0..0.002 rows=31 loops=3,181,038)

27. 69.522 255.463 ↓ 80.9 689,304 1

Sort (cost=726.8..748.1 rows=8,522 width=32) (actual time=189.474..255.463 rows=689,304 loops=1)

  • Sort Key: p_1.id
  • Sort Method: quicksort Memory: 1085kB
  • Buffers: shared hit=7930
28. 185.941 185.941 ↓ 1.1 8,962 1

CTE Scan on player_ids p_1 (cost=0..170.44 rows=8,522 width=32) (actual time=149.888..185.941 rows=8,962 loops=1)

  • Buffers: shared hit=7930
29.          

CTE transactions

30. 5.776 190.659 ↑ 206.2 1,552 1

Aggregate (cost=1,177,279.3..1,245,826.81 rows=320,000 width=136) (actual time=184.19..190.659 rows=1,552 loops=1)

  • Buffers: shared hit=19518
31.          

Initplan (forAggregate)

32. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on adjustment adjustment_1 (cost=0..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)

33. 9.719 184.881 ↑ 295.9 9,213 1

Sort (cost=1,177,279.28..1,184,095.67 rows=2,726,556 width=41) (actual time=184.126..184.881 rows=9,213 loops=1)

  • Sort Key: t.currency, p_2.agent_id, pp_1.start_date, pp_1.end_date
  • Sort Method: quicksort Memory: 1104kB
  • Buffers: shared hit=19518
34. 123.401 175.162 ↑ 295.9 9,213 1

Nested Loop (cost=3,941.6..801,954.87 rows=2,726,556 width=41) (actual time=27.728..175.162 rows=9,213 loops=1)

  • Buffers: shared hit=19518
35. 0.022 0.022 ↑ 32.3 31 1

CTE Scan on periods pp_1 (cost=0..20 rows=1,000 width=16) (actual time=0.002..0.022 rows=31 loops=1)

36. 15.089 51.739 ↑ 2.6 9,288 31

Materialize (cost=3,941.6..4,478.72 rows=24,539 width=33) (actual time=0.889..1.669 rows=9,288 loops=31)

  • Buffers: shared hit=19518
37. 4.209 36.650 ↑ 2.6 9,288 1

Merge Join (cost=3,941.6..4,356.02 rows=24,539 width=33) (actual time=27.547..36.65 rows=9,288 loops=1)

  • Buffers: shared hit=19518
38. 4.547 5.977 ↓ 1.1 8,962 1

Sort (cost=726.8..748.1 rows=8,522 width=32) (actual time=4.423..5.977 rows=8,962 loops=1)

  • Sort Key: p_2.id
  • Sort Method: quicksort Memory: 1085kB
39. 1.430 1.430 ↓ 1.1 8,962 1

CTE Scan on player_ids p_2 (cost=0..170.44 rows=8,522 width=32) (actual time=0.002..1.43 rows=8,962 loops=1)

40. 12.990 26.464 ↑ 1.1 23,489 1

Sort (cost=3,214.8..3,278.01 rows=25,285 width=33) (actual time=23.107..26.464 rows=23,489 loops=1)

  • Sort Key: t.casino_player_id
  • Sort Method: quicksort Memory: 2604kB
  • Buffers: shared hit=19518
41. 13.474 13.474 ↑ 1.1 23,491 1

Index Scan using ix_player_transactions_transaction_date on player_transactions t (cost=0.29..1,365.71 rows=25,285 width=33) (actual time=0.026..13.474 rows=23,491 loops=1)

  • Index Cond: ((t.transaction_date >= '2019-04-01 00:00:00+08'::timestamp with time zone) AND (t.transaction_date < '2019-05-01 00:00:00+08'::timestamp with time zone))
  • Buffers: shared hit=19518
42.          

CTE data_mid

43. 1.688 61,591.974 ↑ 250.2 1,599 1

Merge Join (cost=131,468.62..141,469.42 rows=400,000 width=364) (actual time=61,590..61,591.974 rows=1,599 loops=1)

  • Buffers: shared hit=4704966, temp read=7615 written=7615
44. 2.354 61,396.993 ↑ 321.0 1,246 1

Sort (cost=95,808.28..96,808.28 rows=400,000 width=268) (actual time=61,396.819..61,396.993 rows=1,246 loops=1)

  • Sort Key: gh_1.start_date, gh_1.end_date, gh_1.currency, gh_1.agent_id
  • Sort Method: quicksort Memory: 224kB
  • Buffers: shared hit=4685448, temp read=7615 written=7615
45. 61,394.639 61,394.639 ↑ 321.0 1,246 1

CTE Scan on game_history gh_1 (cost=0..8,000 rows=400,000 width=268) (actual time=60,588.944..61,394.639 rows=1,246 loops=1)

  • Buffers: shared hit=4685448, temp read=7615 written=7615
46. 1.829 193.293 ↑ 206.2 1,552 1

Sort (cost=35,660.34..36,460.34 rows=320,000 width=112) (actual time=193.155..193.293 rows=1,552 loops=1)

  • Sort Key: tr.start_date, tr.end_date, tr.currency, tr.agent_id
  • Sort Method: quicksort Memory: 250kB
  • Buffers: shared hit=19518
47. 191.464 191.464 ↑ 206.2 1,552 1

CTE Scan on transactions tr (cost=0..6,400 rows=320,000 width=112) (actual time=184.196..191.464 rows=1,552 loops=1)

  • Buffers: shared hit=19518
48.          

CTE data

49. 2.837 61,595.707 ↑ 25.0 1,599 1

Aggregate (cost=25,000..25,600 rows=40,000 width=364) (actual time=61,594.988..61,595.707 rows=1,599 loops=1)

  • Buffers: shared hit=4704966, temp read=7615 written=7615
50. 61,592.870 61,592.870 ↑ 250.2 1,599 1

CTE Scan on data_mid d_1 (cost=0..8,000 rows=400,000 width=364) (actual time=61,590.006..61,592.87 rows=1,599 loops=1)

  • Buffers: shared hit=4704966, temp read=7615 written=7615
51. 0.738 61,599.007 ↑ 9,660.0 10 1

Aggregate (cost=12,276.43..29,905.93 rows=96,600 width=536) (actual time=61,598.345..61,599.007 rows=10 loops=1)

  • Buffers: shared hit=4704969, temp read=7615 written=7615
52. 0.817 61,598.269 ↑ 187.9 514 1

Sort (cost=12,276.43..12,517.93 rows=96,600 width=348) (actual time=61,598.211..61,598.269 rows=514 loops=1)

  • Sort Key: d.start_date DESC, d.currency, d.end_date, "*VALUES*".column2
  • Sort Method: quicksort Memory: 273kB
  • Buffers: shared hit=4704969, temp read=7615 written=7615
53. 0.623 61,597.452 ↑ 60.4 1,599 1

Hash Join (cost=12.08..4,278.07 rows=96,600 width=348) (actual time=61,595.313..61,597.452 rows=1,599 loops=1)

  • Buffers: shared hit=4704966, temp read=7615 written=7615
54. 61,596.554 61,596.554 ↑ 25.0 1,599 1

CTE Scan on data d (cost=0..800 rows=40,000 width=332) (actual time=61,594.993..61,596.554 rows=1,599 loops=1)

  • Buffers: shared hit=4704966, temp read=7615 written=7615
55. 0.084 0.275 ↑ 1.0 483 1

Hash (cost=6.04..6.04 rows=483 width=48) (actual time=0.275..0.275 rows=483 loops=1)

56. 0.191 0.191 ↑ 1.0 483 1

Values Scan (cost=0..6.04 rows=483 width=48) (actual time=0.004..0.191 rows=483 loops=1)