explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pxpl : Optimization for: plan #qMNT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 208,476.730 ↑ 4.4 7 1

Limit (cost=3,099,827.24..3,099,833.72 rows=31 width=860) (actual time=208,476.608..208,476.730 rows=7 loops=1)

2. 0.294 208,476.724 ↑ 77.0 7 1

Merge Left Join (cost=3,099,827.24..3,099,939.85 rows=539 width=860) (actual time=208,476.605..208,476.724 rows=7 loops=1)

  • Merge Cond: (conso.date1 = vu.date)
3. 0.009 208,276.723 ↑ 77.0 7 1

Merge Left Join (cost=3,095,673.58..3,095,676.47 rows=539 width=276) (actual time=208,276.704..208,276.723 rows=7 loops=1)

  • Merge Cond: (conso.date1 = rtb.date)
4. 0.039 208,274.679 ↑ 77.0 7 1

Sort (cost=3,094,711.69..3,094,713.04 rows=539 width=252) (actual time=208,274.664..208,274.679 rows=7 loops=1)

  • Sort Key: conso.date1 DESC
  • Sort Method: quicksort Memory: 26kB
5. 0.004 208,274.640 ↑ 77.0 7 1

Subquery Scan on conso (cost=3,094,667.02..3,094,687.23 rows=539 width=252) (actual time=208,274.584..208,274.640 rows=7 loops=1)

6. 2,778.498 208,274.636 ↑ 77.0 7 1

HashAggregate (cost=3,094,667.02..3,094,681.84 rows=539 width=252) (actual time=208,274.582..208,274.636 rows=7 loops=1)

  • Group Key: cc.date
7. 358.712 205,496.138 ↑ 22.3 1,243,445 1

Merge Left Join (cost=876,360.83..1,359,823.90 rows=27,757,490 width=119) (actual time=202,004.577..205,496.138 rows=1,243,445 loops=1)

  • Merge Cond: (cam.id = cf.campaign_id)
8. 690.105 204,958.812 ↑ 1.5 1,243,445 1

Nested Loop Left Join (cost=875,994.68..938,350.53 rows=1,897,948 width=123) (actual time=201,946.183..204,958.812 rows=1,243,445 loops=1)

  • Join Filter: (cc.date = crh.date)
  • Rows Removed by Join Filter: 7460670
9. 379.605 203,025.262 ↑ 1.5 1,243,445 1

Merge Join (cost=875,894.68..909,741.43 rows=1,897,948 width=103) (actual time=201,885.239..203,025.262 rows=1,243,445 loops=1)

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

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

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

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

12. 188.440 202,556.973 ↑ 1.6 1,243,445 1

Materialize (cost=875,528.54..885,430.31 rows=1,980,354 width=103) (actual time=201,799.133..202,556.973 rows=1,243,445 loops=1)

13. 3,251.203 202,368.533 ↑ 1.6 1,243,445 1

Sort (cost=875,528.54..880,479.42 rows=1,980,354 width=103) (actual time=201,799.124..202,368.533 rows=1,243,445 loops=1)

  • Sort Key: cc.campaign_id
  • Sort Method: external merge Disk: 125248kB
14. 862.382 199,117.330 ↑ 1.6 1,243,445 1

Hash Join (cost=432.55..478,104.38 rows=1,980,354 width=103) (actual time=19.139..199,117.330 rows=1,243,445 loops=1)

  • Hash Cond: (z.media_id = m.id)
15. 837.236 198,243.866 ↓ 3.2 1,243,445 1

Nested Loop (cost=100.57..407,686.41 rows=386,788 width=107) (actual time=7.986..198,243.866 rows=1,243,445 loops=1)

16. 209.994 209.994 ↓ 21.7 27,782 1

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

17. 197,196.636 197,196.636 ↑ 6.7 45 27,782

Index Scan using conso_commission_zone_ix on conso_commission cc (cost=0.57..315.37 rows=302 width=75) (actual time=0.175..7.098 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.340 11.082 ↓ 1.6 1,667 1

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

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

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

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

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

21. 0.010 1.508 ↓ 4.9 54 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.044 1.498 ↓ 4.9 54 1

HashAggregate (cost=138.69..138.80 rows=11 width=12) (actual time=1.489..1.498 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.449..1.454 rows=54 loops=1)

24. 1,182.538 1,243.445 ↓ 7.0 7 1,243,445

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

25. 60.907 60.907 ↓ 7.0 7 1

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

26. 125.007 178.614 ↓ 322.4 942,888 1

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

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

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

28. 0.020 2.035 ↓ 0.0 0 1

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

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

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

30. 0.004 2.013 ↓ 0.0 0 1

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

  • Group Key: wrc.date
31. 0.042 2.009 ↓ 0.0 0 1

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

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

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

33. 0.002 0.853 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.007 0.851 ↓ 0.0 0 1

Hash Join (cost=442.36..536.13 rows=328 width=20) (actual time=0.851..0.851 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.000 0.844 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.009 0.844 ↓ 0.0 0 1

Hash Join (cost=105.59..202.63 rows=64 width=24) (actual time=0.844..0.844 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.835 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.058 0.835 ↓ 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.835..0.835 rows=0 loops=1)

  • Recheck Cond: ((date >= '2019-03-07'::date) AND (date <= '2019-03-13'::date))
45. 0.777 0.777 ↓ 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.777..0.777 rows=0 loops=1)

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

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

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

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

48. 2.108 199.669 ↑ 2.0 7 1

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

  • Group Key: cvc.date
49. 10.073 197.561 ↑ 1.6 9,577 1

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

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

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

51. 3.178 116.614 ↑ 1.0 9,577 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
52. 113.436 113.436 ↑ 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.742..113.436 rows=9,577 loops=1)

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