explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BcXO

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 125,422.549 ↑ 4.4 7 1

Limit (cost=4,035,114.20..4,035,120.68 rows=31 width=860) (actual time=125,422.413..125,422.549 rows=7 loops=1)

2. 0.268 125,422.547 ↑ 77.1 7 1

Merge Left Join (cost=4,035,114.20..4,035,227.03 rows=540 width=860) (actual time=125,422.411..125,422.547 rows=7 loops=1)

  • Merge Cond: (conso.date1 = vu.date)
3. 0.040 125,210.173 ↑ 77.1 7 1

Merge Left Join (cost=4,030,960.54..4,030,963.45 rows=540 width=276) (actual time=125,210.152..125,210.173 rows=7 loops=1)

  • Merge Cond: (conso.date1 = rtb.date)
4. 0.039 125,208.920 ↑ 77.1 7 1

Sort (cost=4,029,998.65..4,030,000.00 rows=540 width=252) (actual time=125,208.905..125,208.920 rows=7 loops=1)

  • Sort Key: conso.date1 DESC
  • Sort Method: quicksort Memory: 26kB
5. 0.005 125,208.881 ↑ 77.1 7 1

Subquery Scan on conso (cost=4,029,953.89..4,029,974.14 rows=540 width=252) (actual time=125,208.837..125,208.881 rows=7 loops=1)

6. 2,688.002 125,208.876 ↑ 77.1 7 1

HashAggregate (cost=4,029,953.89..4,029,968.74 rows=540 width=252) (actual time=125,208.834..125,208.876 rows=7 loops=1)

  • Group Key: cc.date
7. 340.701 122,520.874 ↑ 28.6 1,243,445 1

Merge Left Join (cost=1,188,341.76..1,807,506.01 rows=35,559,166 width=119) (actual time=119,100.884..122,520.874 rows=1,243,445 loops=1)

  • Merge Cond: (cam.id = cf.campaign_id)
8. 1,850.852 122,015.841 ↑ 2.0 1,243,445 1

Nested Loop Left Join (cost=1,187,975.61..1,267,673.89 rows=2,431,396 width=123) (actual time=119,043.913..122,015.841 rows=1,243,445 loops=1)

  • Join Filter: (cc.date = crh.date)
  • Rows Removed by Join Filter: 7460670
9. 384.019 120,164.989 ↑ 2.0 1,243,445 1

Merge Join (cost=1,187,875.61..1,231,063.07 rows=2,431,396 width=103) (actual time=119,041.903..120,164.989 rows=1,243,445 loops=1)

  • Merge Cond: (cam.id = cc.campaign_id)
10. 6.967 88.021 ↓ 6.1 17,908 1

Sort (cost=366.15..373.46 rows=2,925 width=4) (actual time=85.330..88.021 rows=17,908 loops=1)

  • Sort Key: cam.id
  • Sort Method: quicksort Memory: 1278kB
11. 81.054 81.054 ↓ 6.1 17,934 1

Foreign Scan on campaign cam (cost=100.00..197.75 rows=2,925 width=4) (actual time=2.413..81.054 rows=17,934 loops=1)

12. 191.315 119,692.949 ↑ 2.0 1,243,445 1

Materialize (cost=1,187,509.47..1,200,082.06 rows=2,514,519 width=103) (actual time=118,956.556..119,692.949 rows=1,243,445 loops=1)

13. 3,088.056 119,501.634 ↑ 2.0 1,243,445 1

Sort (cost=1,187,509.47..1,193,795.77 rows=2,514,519 width=103) (actual time=118,956.549..119,501.634 rows=1,243,445 loops=1)

  • Sort Key: cc.campaign_id
  • Sort Method: external merge Disk: 125248kB
14. 733.197 116,413.578 ↑ 2.0 1,243,445 1

Hash Join (cost=432.55..598,014.83 rows=2,514,519 width=103) (actual time=19.653..116,413.578 rows=1,243,445 loops=1)

  • Hash Cond: (z.media_id = m.id)
15. 760.041 115,669.209 ↓ 2.5 1,243,445 1

Nested Loop (cost=100.57..508,692.44 rows=491,117 width=107) (actual time=8.415..115,669.209 rows=1,243,445 loops=1)

16. 197.290 197.290 ↓ 21.7 27,782 1

Foreign Scan on zone z (cost=100.00..148.40 rows=1,280 width=40) (actual time=1.618..197.290 rows=27,782 loops=1)

17. 114,711.878 114,711.878 ↑ 8.5 45 27,782

Index Scan using conso_commission_zone_ix on conso_commission cc (cost=0.57..393.46 rows=384 width=75) (actual time=0.158..4.129 rows=45 loops=27,782)

  • Index Cond: ((zone_id = z.id) AND (date >= '2019-03-07'::date) AND (date <= '2019-03-13'::date))
18. 0.452 11.172 ↓ 1.6 1,667 1

Hash (cost=319.18..319.18 rows=1,024 width=4) (actual time=11.172..11.172 rows=1,667 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 75kB
19. 0.764 10.720 ↓ 1.6 1,667 1

Hash Join (cost=238.93..319.18 rows=1,024 width=4) (actual time=3.065..10.720 rows=1,667 loops=1)

  • Hash Cond: ((m.country_ref)::text = (fuco.country_ref)::text)
20. 8.454 8.454 ↑ 1.2 1,667 1

Foreign Scan on media m (cost=100.00..171.44 rows=2,048 width=16) (actual time=1.502..8.454 rows=1,667 loops=1)

21. 0.017 1.502 ↓ 4.9 54 1

Hash (cost=138.80..138.80 rows=11 width=12) (actual time=1.502..1.502 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.031 1.485 ↓ 4.9 54 1

HashAggregate (cost=138.69..138.80 rows=11 width=12) (actual time=1.478..1.485 rows=54 loops=1)

  • Group Key: (fuco.country_ref)::text
23. 1.454 1.454 ↓ 4.9 54 1

Foreign Scan on flex_user_countries fuco (cost=100.00..138.66 rows=11 width=12) (actual time=1.450..1.454 rows=54 loops=1)

24. 0.000 0.000 ↓ 7.0 7 1,243,445

Materialize (cost=100.00..139.88 rows=1 width=24) (actual time=0.000..0.000 rows=7 loops=1,243,445)

25. 1.988 1.988 ↓ 7.0 7 1

Foreign Scan on conversion_rate_history crh (cost=100.00..139.88 rows=1 width=24) (actual time=1.984..1.988 rows=7 loops=1)

26. 112.547 164.332 ↓ 322.4 942,888 1

Sort (cost=366.15..373.46 rows=2,925 width=4) (actual time=56.965..164.332 rows=942,888 loops=1)

  • Sort Key: cf.campaign_id
  • Sort Method: quicksort Memory: 1080kB
27. 51.785 51.785 ↓ 5.1 14,836 1

Foreign Scan on campaign_flag cf (cost=100.00..197.75 rows=2,925 width=4) (actual time=1.125..51.785 rows=14,836 loops=1)

28. 0.013 1.213 ↓ 0.0 0 1

Sort (cost=961.90..961.91 rows=5 width=28) (actual time=1.213..1.213 rows=0 loops=1)

  • Sort Key: rtb.date DESC
  • Sort Method: quicksort Memory: 25kB
29. 0.001 1.200 ↓ 0.0 0 1

Subquery Scan on rtb (cost=961.74..961.84 rows=5 width=28) (actual time=1.199..1.200 rows=0 loops=1)

30. 0.005 1.199 ↓ 0.0 0 1

HashAggregate (cost=961.74..961.79 rows=5 width=28) (actual time=1.199..1.199 rows=0 loops=1)

  • Group Key: wrc.date
31. 0.023 1.194 ↓ 0.0 0 1

Hash Join (cost=640.23..913.92 rows=4,782 width=16) (actual time=1.194..1.194 rows=0 loops=1)

  • Hash Cond: (cam_1.id = wrc.campaign_id)
32. 1.092 1.092 ↑ 2,925.0 1 1

Foreign Scan on campaign cam_1 (cost=100.00..197.75 rows=2,925 width=4) (actual time=1.092..1.092 rows=1 loops=1)

33. 0.000 0.079 ↓ 0.0 0 1

Hash (cost=536.13..536.13 rows=328 width=20) (actual time=0.079..0.079 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.022 0.079 ↓ 0.0 0 1

Hash Join (cost=442.36..536.13 rows=328 width=20) (actual time=0.079..0.079 rows=0 loops=1)

  • Hash Cond: (m_1.id = z_1.media_id)
35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=238.93..319.18 rows=1,024 width=4) (never executed)

  • Hash Cond: ((m_1.country_ref)::text = (fuco_1.country_ref)::text)
36. 0.000 0.000 ↓ 0.0 0

Foreign Scan on media m_1 (cost=100.00..171.44 rows=2,048 width=16) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=138.80..138.80 rows=11 width=12) (never executed)

38. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=138.69..138.80 rows=11 width=12) (never executed)

  • Group Key: (fuco_1.country_ref)::text
39. 0.000 0.000 ↓ 0.0 0

Foreign Scan on flex_user_countries fuco_1 (cost=100.00..138.66 rows=11 width=12) (never executed)

40. 0.001 0.057 ↓ 0.0 0 1

Hash (cost=202.63..202.63 rows=64 width=24) (actual time=0.057..0.057 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.007 0.056 ↓ 0.0 0 1

Hash Join (cost=105.59..202.63 rows=64 width=24) (actual time=0.056..0.056 rows=0 loops=1)

  • Hash Cond: (z_1.id = wrc.zone_id)
42. 0.000 0.000 ↓ 0.0 0

Foreign Scan on zone z_1 (cost=100.00..186.80 rows=2,560 width=8) (never executed)

43. 0.000 0.049 ↓ 0.0 0 1

Hash (cost=5.52..5.52 rows=5 width=24) (actual time=0.049..0.049 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.004 0.049 ↓ 0.0 0 1

Bitmap Heap Scan on rtb_zone_winrate_campaign wrc (cost=1.30..5.52 rows=5 width=24) (actual time=0.049..0.049 rows=0 loops=1)

  • Recheck Cond: ((date >= '2019-03-07'::date) AND (date <= '2019-03-13'::date))
45. 0.045 0.045 ↓ 0.0 0 1

Bitmap Index Scan on rtb_zone_winrate_campaign_ix (cost=0.00..1.30 rows=5 width=0) (actual time=0.045..0.045 rows=0 loops=1)

  • Index Cond: ((date >= '2019-03-07'::date) AND (date <= '2019-03-13'::date))
46. 0.016 212.106 ↑ 2.0 7 1

Sort (cost=4,153.66..4,153.69 rows=14 width=12) (actual time=212.104..212.106 rows=7 loops=1)

  • Sort Key: vu.date DESC
  • Sort Method: quicksort Memory: 25kB
47. 0.004 212.090 ↑ 2.0 7 1

Subquery Scan on vu (cost=4,153.11..4,153.39 rows=14 width=12) (actual time=212.086..212.090 rows=7 loops=1)

48. 2.088 212.086 ↑ 2.0 7 1

HashAggregate (cost=4,153.11..4,153.25 rows=14 width=12) (actual time=212.085..212.086 rows=7 loops=1)

  • Group Key: cvc.date
49. 10.592 209.998 ↑ 1.6 9,577 1

Hash Join (cost=3,766.24..4,076.24 rows=15,375 width=8) (actual time=106.368..209.998 rows=9,577 loops=1)

  • Hash Cond: (cam_2.id = cvc.campaign_id)
50. 94.129 94.129 ↓ 6.1 17,934 1

Foreign Scan on campaign cam_2 (cost=100.00..197.75 rows=2,925 width=4) (actual time=1.032..94.129 rows=17,934 loops=1)

51. 2.962 105.277 ↑ 1.0 9,577 1

Hash (cost=3,546.53..3,546.53 rows=9,577 width=12) (actual time=105.277..105.277 rows=9,577 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
52. 102.315 102.315 ↑ 1.0 9,577 1

Seq Scan on conso_vu_campaign cvc (cost=0.00..3,546.53 rows=9,577 width=12) (actual time=0.647..102.315 rows=9,577 loops=1)

  • Filter: ((date >= '2019-03-07'::date) AND (date <= '2019-03-13'::date))
  • Rows Removed by Filter: 9068