explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vCCw

Settings
# exclusive inclusive rows x rows loops node
1. 0.307 4,838,734.219 ↓ 392.0 392 1

Limit (cost=247,104,405.32..252,809,567.98 rows=1 width=96) (actual time=4,356,576.194..4,838,734.219 rows=392 loops=1)

  • Buffers: shared hit=446634128 read=50954647 dirtied=7 written=36776, temp read=10724796 written=7448458
2. 1.895 4,838,733.912 ↓ 392.0 392 1

Nested Loop (cost=247,104,405.32..252,809,567.98 rows=1 width=96) (actual time=4,356,576.194..4,838,733.912 rows=392 loops=1)

  • Join Filter: (COALESCE((COALESCE(ds.bet_client_id, pa12_1.bet_client_id)), pa12.bet_client_id) = a13.bet_client_id)
  • Rows Removed by Join Filter: 3920
  • Buffers: shared hit=446634128 read=50954647 dirtied=7 written=36776, temp read=10724796 written=7448458
3. 1.334 4,838,731.233 ↓ 39.2 392 1

Hash Full Join (cost=247,104,405.32..252,809,565.68 rows=10 width=84) (actual time=4,356,576.173..4,838,731.233 rows=392 loops=1)

  • Hash Cond: ((COALESCE(ds.bet_client_id, pa12_1.bet_client_id) = pa12.bet_client_id) AND (COALESCE(a12.day, pa12_1.days) = pa12.days))
  • Buffers: shared hit=446634127 read=50954647 dirtied=7 written=36776, temp read=10724796 written=7448458
4. 3.308 3,086,376.001 ↓ 39.2 392 1

Hash Full Join (cost=182,503,262.16..188,208,422.44 rows=10 width=82) (actual time=2,604,222.255..3,086,376.001 rows=392 loops=1)

  • Hash Cond: ((ds.bet_client_id = pa12_1.bet_client_id) AND (a12.day = pa12_1.days))
  • Buffers: shared hit=239747213 read=34258403 written=20411, temp read=8537809 written=5974369
5. 367,513.077 2,063,176.024 ↓ 38.6 386 1

GroupAggregate (cost=57,747,687.18..63,452,847.27 rows=10 width=56) (actual time=1,581,025.554..2,063,176.024 rows=386 loops=1)

  • Group Key: a12.day, ds.bet_client_id
  • Buffers: shared hit=205154237 read=18368931, temp read=4874665 written=3719171
6. 404,146.762 1,695,662.947 ↓ 1.6 198,323,812 1

Sort (cost=57,747,687.18..58,064,640.51 rows=126,781,331 width=56) (actual time=1,581,024.934..1,695,662.947 rows=198,323,812 loops=1)

  • Sort Key: a12.day, ds.bet_client_id
  • Sort Method: external merge Disk: 14346008kB
  • Buffers: shared hit=205154229 read=18368931, temp read=4874665 written=3719171
7. 43,890.124 1,291,516.185 ↓ 1.6 198,323,812 1

Hash Join (cost=19,164,385.09..39,446,236.49 rows=126,781,331 width=56) (actual time=408,245.910..1,291,516.185 rows=198,323,812 loops=1)

  • Hash Cond: (ds.bet_client_id = a13_1.bet_client_id)
  • Buffers: shared hit=205154229 read=18368931, temp read=3081413 written=1925919
8. 145,671.444 1,247,626.047 ↓ 1.4 198,402,662 1

Merge Join (cost=19,164,384.33..37,655,449.43 rows=139,459,464 width=56) (actual time=408,108.270..1,247,626.047 rows=198,402,662 loops=1)

  • Merge Cond: (ds.first_settlement_id = s.settlement_id)
  • Buffers: shared hit=205154228 read=18368931, temp read=3081413 written=1925919
9. 666,744.283 666,744.283 ↑ 1.0 491,821,253 1

Index Scan using denormalized_selections_first_settlement_id_idx2 on denormalized_selections ds (cost=0.57..15,321,894.34 rows=495,321,952 width=42) (actual time=0.021..666,744.283 rows=491,821,253 loops=1)

  • Buffers: shared hit=204992814 read=14436212
10. 128,179.015 435,210.320 ↓ 3.2 200,134,421 1

Sort (cost=19,164,314.93..19,318,432.20 rows=61,646,909 width=26) (actual time=408,108.238..435,210.320 rows=200,134,421 loops=1)

  • Sort Key: s.settlement_id
  • Sort Method: external sort Disk: 3602184kB
  • Buffers: shared hit=161414 read=3932719, temp read=2503669 written=1925919
11. 65,288.606 307,031.305 ↓ 1.4 87,738,840 1

Hash Join (cost=7,676,283.55..11,187,967.72 rows=61,646,909 width=26) (actual time=241,740.712..307,031.305 rows=87,738,840 loops=1)

  • Hash Cond: (a12.days = (timezone('Europe/Stockholm'::text, s.created_at))::date)
  • Buffers: shared hit=161414 read=3932719, temp read=1025378 written=1025372
12. 12.574 12.574 ↓ 1.5 1,248 1

Index Scan using dim_mtd_mtd_date_idx on dim_mtd a12 (cost=0.43..18.89 rows=826 width=8) (actual time=4.603..12.574 rows=1,248 loops=1)

  • Index Cond: (mtd_date = to_date('2019-05-12'::text, 'YYYY-MM-DD'::text))
  • Buffers: shared hit=10 read=16
13. 121,409.972 241,730.125 ↓ 1.0 217,443,491 1

Hash (cost=4,218,994.22..4,218,994.22 rows=217,194,072 width=30) (actual time=241,730.125..241,730.125 rows=217,443,491 loops=1)

  • Buckets: 8388608 Batches: 4 Memory Usage: 3550131kB
  • Buffers: shared hit=161404 read=3932703, temp written=1025364
14. 120,320.153 120,320.153 ↓ 1.0 217,443,491 1

Seq Scan on settlements s (cost=0.00..4,218,994.22 rows=217,194,072 width=30) (actual time=0.451..120,320.153 rows=217,443,491 loops=1)

  • Filter: (settlement_id IS NOT NULL)
  • Buffers: shared hit=161404 read=3932703
15. 0.002 0.014 ↑ 1.0 10 1

Hash (cost=0.64..0.64 rows=10 width=2) (actual time=0.014..0.014 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
16. 0.012 0.012 ↑ 1.0 10 1

Seq Scan on bet_clients a13_1 (cost=0.00..0.64 rows=10 width=2) (actual time=0.010..0.012 rows=10 loops=1)

  • Filter: (cname <> 'web-flash'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
17. 0.065 1,023,196.669 ↓ 33.0 330 1

Hash (cost=124,755,574.83..124,755,574.83 rows=10 width=14) (actual time=1,023,196.669..1,023,196.669 rows=330 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=34592976 read=15889472 written=20411, temp read=3663144 written=2255198
18. 0.031 1,023,196.604 ↓ 33.0 330 1

Subquery Scan on pa12_1 (cost=124,755,574.61..124,755,574.83 rows=10 width=14) (actual time=1,023,196.489..1,023,196.604 rows=330 loops=1)

  • Buffers: shared hit=34592976 read=15889472 written=20411, temp read=3663144 written=2255198
19. 1,710.665 1,023,196.573 ↓ 33.0 330 1

HashAggregate (cost=124,755,574.61..124,755,574.73 rows=10 width=28) (actual time=1,023,196.487..1,023,196.573 rows=330 loops=1)

  • Group Key: a12_1.day, ds_1.bet_client_id
  • Buffers: shared hit=34592976 read=15889472 written=20411, temp read=3663144 written=2255198
20. 876.076 1,021,485.908 ↑ 149.7 3,526,862 1

Hash Join (cost=19,376,555.53..112,877,562.55 rows=527,911,647 width=28) (actual time=494,742.148..1,021,485.908 rows=3,526,862 loops=1)

  • Hash Cond: (ds_1.bet_client_id = a13_2.bet_client_id)
  • Buffers: shared hit=34592976 read=15889472 written=20411, temp read=3663144 written=2255198
21. 91,367.335 1,020,609.802 ↑ 163.5 3,551,870 1

Merge Join (cost=19,376,554.77..105,420,809.77 rows=580,702,812 width=28) (actual time=494,619.322..1,020,609.802 rows=3,551,870 loops=1)

  • Merge Cond: (ds_1.placement_id = s_1.placement_id)
  • Join Filter: (s_1.settlement_id <> ds_1.first_settlement_id)
  • Rows Removed by Join Filter: 199509458
  • Buffers: shared hit=34592975 read=15889472 written=20411, temp read=3663144 written=2255198
22. 406,885.736 406,885.736 ↓ 1.0 495,598,927 1

Index Scan using denormalized_selections_placement_id_first_settlement_id_idx on denormalized_selections ds_1 (cost=0.57..74,751,040.00 rows=495,321,952 width=22) (actual time=0.026..406,885.736 rows=495,598,927 loops=1)

  • Buffers: shared hit=34147301 read=12241013 written=20408
23. 182,457.410 522,356.731 ↓ 3.3 203,061,328 1

Sort (cost=19,376,417.93..19,530,535.20 rows=61,646,909 width=34) (actual time=494,618.971..522,356.731 rows=203,061,328 loops=1)

  • Sort Key: s_1.placement_id
  • Sort Method: external sort Disk: 4288312kB
  • Buffers: shared hit=445674 read=3648459 written=3, temp read=2959194 written=2255198
24. 79,114.911 339,899.321 ↓ 1.4 87,738,840 1

Hash Join (cost=7,782,335.05..11,400,070.72 rows=61,646,909 width=34) (actual time=260,819.053..339,899.321 rows=87,738,840 loops=1)

  • Hash Cond: (a12_1.days = (timezone('Europe/Stockholm'::text, s_1.created_at))::date)
  • Buffers: shared hit=445674 read=3648459 written=3, temp read=1183125 written=1183119
25. 6.780 6.780 ↓ 1.5 1,248 1

Index Scan using dim_mtd_mtd_date_idx on dim_mtd a12_1 (cost=0.43..18.89 rows=826 width=8) (actual time=2.264..6.780 rows=1,248 loops=1)

  • Index Cond: (mtd_date = to_date('2019-05-12'::text, 'YYYY-MM-DD'::text))
  • Buffers: shared hit=14 read=12
26. 129,138.683 260,777.630 ↓ 1.0 217,443,491 1

Hash (cost=4,218,994.22..4,218,994.22 rows=217,194,072 width=38) (actual time=260,777.630..260,777.630 rows=217,443,491 loops=1)

  • Buckets: 8388608 Batches: 4 Memory Usage: 3993897kB
  • Buffers: shared hit=445660 read=3648447 written=3, temp written=1183111
27. 131,638.947 131,638.947 ↓ 1.0 217,443,491 1

Seq Scan on settlements s_1 (cost=0.00..4,218,994.22 rows=217,194,072 width=38) (actual time=0.475..131,638.947 rows=217,443,491 loops=1)

  • Buffers: shared hit=445660 read=3648447 written=3
28. 0.004 0.030 ↑ 1.0 10 1

Hash (cost=0.64..0.64 rows=10 width=2) (actual time=0.030..0.030 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
29. 0.026 0.026 ↑ 1.0 10 1

Seq Scan on bet_clients a13_2 (cost=0.00..0.64 rows=10 width=2) (actual time=0.026..0.026 rows=10 loops=1)

  • Filter: (cname <> 'web-flash'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
30. 0.858 1,752,353.898 ↓ 38.6 386 1

Hash (cost=64,601,143.00..64,601,143.00 rows=10 width=14) (actual time=1,752,353.898..1,752,353.898 rows=386 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=206886914 read=16696244 dirtied=7 written=16365, temp read=2186987 written=1474089
31. 0.440 1,752,353.040 ↓ 38.6 386 1

Subquery Scan on pa12 (cost=63,333,329.49..64,601,143.00 rows=10 width=14) (actual time=1,641,297.193..1,752,353.040 rows=386 loops=1)

  • Buffers: shared hit=206886914 read=16696244 dirtied=7 written=16365, temp read=2186987 written=1474089
32. 36,742.290 1,752,352.600 ↓ 38.6 386 1

GroupAggregate (cost=63,333,329.49..64,601,142.90 rows=10 width=8) (actual time=1,641,297.192..1,752,352.600 rows=386 loops=1)

  • Group Key: a13_3.day, ds_2.bet_client_id
  • Buffers: shared hit=206886914 read=16696244 dirtied=7 written=16365, temp read=2186987 written=1474089
33. 318,488.715 1,715,610.310 ↓ 1.6 198,323,812 1

Sort (cost=63,333,329.49..63,650,282.82 rows=126,781,331 width=8) (actual time=1,641,297.126..1,715,610.310 rows=198,323,812 loops=1)

  • Sort Key: a13_3.day, ds_2.bet_client_id
  • Sort Method: external merge Disk: 3489576kB
  • Buffers: shared hit=206886914 read=16696244 dirtied=7 written=16365, temp read=2186987 written=1474089
34. 298,532.464 1,397,121.595 ↓ 1.6 198,323,812 1

Hash Join (cost=18,855,594.92..46,269,977.81 rows=126,781,331 width=8) (actual time=340,267.587..1,397,121.595 rows=198,323,812 loops=1)

  • Hash Cond: ((ds_2.customer_id)::text = (c.customer_id)::text)
  • Buffers: shared hit=206886908 read=16696244 dirtied=7 written=16365, temp read=1750789 written=1037891
35. 39,322.723 1,095,504.856 ↓ 1.6 198,323,812 1

Hash Join (cost=18,740,177.09..39,023,110.11 rows=126,781,331 width=22) (actual time=337,181.236..1,095,504.856 rows=198,323,812 loops=1)

  • Hash Cond: (ds_2.bet_client_id = a14.bet_client_id)
  • Buffers: shared hit=206826919 read=16696244 dirtied=7 written=16365, temp read=1750789 written=1037891
36. 137,286.184 1,056,182.112 ↓ 1.4 198,402,662 1

Merge Join (cost=18,740,176.33..37,232,323.05 rows=139,459,464 width=22) (actual time=337,095.481..1,056,182.112 rows=198,402,662 loops=1)

  • Merge Cond: (ds_2.first_settlement_id = s_2.settlement_id)
  • Buffers: shared hit=206826918 read=16696244 dirtied=7 written=16365, temp read=1750789 written=1037891
37. 558,645.772 558,645.772 ↑ 1.0 491,821,253 1

Index Scan using denormalized_selections_first_settlement_id_idx2 on denormalized_selections ds_2 (cost=0.57..15,322,986.57 rows=495,321,952 width=22) (actual time=0.017..558,645.772 rows=491,821,253 loops=1)

  • Buffers: shared hit=206075986 read=13353040 written=16360
38. 110,027.410 360,250.156 ↓ 3.2 200,134,421 1

Sort (cost=18,740,106.93..18,894,224.20 rows=61,646,909 width=12) (actual time=337,095.452..360,250.156 rows=200,134,421 loops=1)

  • Sort Key: s_2.settlement_id
  • Sort Method: external sort Disk: 2229920kB
  • Buffers: shared hit=750932 read=3343204 dirtied=7 written=5, temp read=1394341 written=1037891
39. 53,617.526 250,222.746 ↓ 1.4 87,738,840 1

Hash Join (cost=7,464,179.55..10,763,759.72 rows=61,646,909 width=12) (actual time=196,680.298..250,222.746 rows=87,738,840 loops=1)

  • Hash Cond: (a13_3.days = (timezone('Europe/Stockholm'::text, s_2.created_at))::date)
  • Buffers: shared hit=750929 read=3343204 dirtied=7 written=5, temp read=480411 written=480409
40. 5.155 5.155 ↓ 1.5 1,248 1

Index Scan using dim_mtd_mtd_date_idx on dim_mtd a13_3 (cost=0.43..18.89 rows=826 width=8) (actual time=1.898..5.155 rows=1,248 loops=1)

  • Index Cond: (mtd_date = to_date('2019-05-12'::text, 'YYYY-MM-DD'::text))
  • Buffers: shared hit=10 read=16
41. 111,525.036 196,600.065 ↓ 1.0 217,443,491 1

Hash (cost=4,218,994.22..4,218,994.22 rows=217,194,072 width=16) (actual time=196,600.065..196,600.065 rows=217,443,491 loops=1)

  • Buckets: 16777216 Batches: 2 Memory Usage: 5124331kB
  • Buffers: shared hit=750919 read=3343188 dirtied=7 written=5, temp written=480406
42. 85,075.029 85,075.029 ↓ 1.0 217,443,491 1

Seq Scan on settlements s_2 (cost=0.00..4,218,994.22 rows=217,194,072 width=16) (actual time=0.586..85,075.029 rows=217,443,491 loops=1)

  • Filter: (settlement_id IS NOT NULL)
  • Buffers: shared hit=750919 read=3343188 dirtied=7 written=5
43. 0.004 0.021 ↑ 1.0 10 1

Hash (cost=0.64..0.64 rows=10 width=2) (actual time=0.021..0.021 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
44. 0.017 0.017 ↑ 1.0 10 1

Seq Scan on bet_clients a14 (cost=0.00..0.64 rows=10 width=2) (actual time=0.016..0.017 rows=10 loops=1)

  • Filter: (cname <> 'web-flash'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
45. 1,821.650 3,084.275 ↓ 1.0 3,797,838 1

Hash (cost=67,959.59..67,959.59 rows=3,796,659 width=22) (actual time=3,084.275..3,084.275 rows=3,797,838 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 190406kB
  • Buffers: shared hit=59986
46. 1,262.625 1,262.625 ↓ 1.0 3,797,838 1

Seq Scan on customers c (cost=0.00..67,959.59 rows=3,796,659 width=22) (actual time=0.025..1,262.625 rows=3,797,838 loops=1)

  • Buffers: shared hit=59986
47. 0.775 0.784 ↑ 1.0 11 392

Materialize (cost=0.00..0.67 rows=11 width=14) (actual time=0.001..0.002 rows=11 loops=392)

  • Buffers: shared hit=1
48. 0.009 0.009 ↑ 1.0 11 1

Seq Scan on bet_clients a13 (cost=0.00..0.61 rows=11 width=14) (actual time=0.007..0.009 rows=11 loops=1)

  • Buffers: shared hit=1