explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ivz

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 6,810.157 ↑ 1.0 1 1

Aggregate (cost=1,222,917.60..1,222,917.70 rows=1 width=32) (actual time=6,810.157..6,810.157 rows=1 loops=1)

2. 7.120 6,810.138 ↓ 1.4 30 1

Hash Right Join (cost=1,202,712.81..1,222,915.34 rows=22 width=632) (actual time=6,774.268..6,810.138 rows=30 loops=1)

  • Hash Cond: ((dcm.advertiser_id = dbm.advertiser_id) AND (dcm.dcm_day = dbm.day))
3.          

CTE aff

4. 112.992 176.627 ↑ 2.4 39,715 1

HashAggregate (cost=19,271.00..29,133.65 rows=96,221 width=60) (actual time=152.930..176.627 rows=39,715 loops=1)

  • Group Key: advertiser_sales.advertiser_id, date_trunc('day'::text, advertiser_sales.day_transaction)
5. 63.635 63.635 ↓ 1.0 110,955 1

Seq Scan on advertiser_sales (cost=0.00..16,262.50 rows=109,400 width=34) (actual time=0.012..63.635 rows=110,955 loops=1)

6.          

CTE dbm

7. 3,774.562 6,176.836 ↑ 2.0 82,327 1

HashAggregate (cost=1,041,489.44..1,058,116.24 rows=166,268 width=40) (actual time=6,131.190..6,176.836 rows=82,327 loops=1)

  • Group Key: dsp_stats.advertiser_id, dsp_stats.day
8. 2,402.274 2,402.274 ↓ 1.0 7,450,468 1

Seq Scan on dsp_stats (cost=0.00..949,358.50 rows=7,370,475 width=32) (actual time=0.038..2,402.274 rows=7,450,468 loops=1)

9.          

CTE dcm

10. 94.199 165.821 ↑ 3.9 25,004 1

HashAggregate (cost=19,181.28..29,403.14 rows=97,351 width=56) (actual time=149.058..165.821 rows=25,004 loops=1)

  • Group Key: dsp_conversion.advertiser_id, date_trunc('day'::text, dsp_conversion.interaction_date_time)
11. 71.622 71.622 ↓ 1.0 99,879 1

Seq Scan on dsp_conversion (cost=0.00..17,221.56 rows=97,986 width=20) (actual time=0.028..71.622 rows=99,879 loops=1)

12.          

CTE adv_cancelation_rate

13. 0.157 79.741 ↑ 1.6 123 1

GroupAggregate (cost=18,203.24..18,344.30 rows=200 width=44) (actual time=77.464..79.741 rows=123 loops=1)

  • Group Key: advertiser_sales_1.advertiser_id
14. 1.383 79.584 ↑ 1.9 280 1

GroupAggregate (cost=18,203.24..18,266.29 rows=535 width=60) (actual time=77.436..79.584 rows=280 loops=1)

  • Group Key: advertiser_sales_1.advertiser_id, (date_trunc('day'::text, advertiser_sales_1.day_validation))
15. 2.297 78.201 ↓ 6.0 3,291 1

Sort (cost=18,203.24..18,204.61 rows=547 width=26) (actual time=77.403..78.201 rows=3,291 loops=1)

  • Sort Key: advertiser_sales_1.advertiser_id, (date_trunc('day'::text, advertiser_sales_1.day_validation))
  • Sort Method: quicksort Memory: 354kB
16. 75.904 75.904 ↓ 6.0 3,291 1

Seq Scan on advertiser_sales advertiser_sales_1 (cost=0.00..18,178.37 rows=547 width=26) (actual time=0.078..75.904 rows=3,291 loops=1)

  • Filter: ((date_trunc('day'::text, day_validation) <= now()) AND (date_trunc('day'::text, day_validation) >= (('now'::cstring)::date - 30)))
  • Rows Removed by Filter: 107664
17.          

CTE adv_floodlight_interaction

18. 16.917 87.578 ↓ 8.6 16,862 1

HashAggregate (cost=12,813.45..13,014.65 rows=1,963 width=32) (actual time=79.006..87.578 rows=16,862 loops=1)

  • Group Key: a.id, dst.date
19. 9.486 70.661 ↑ 1.2 16,862 1

Group (cost=10,359.57..10,703.11 rows=19,631 width=576) (actual time=55.955..70.661 rows=16,862 loops=1)

  • Group Key: dst.floodlight_activity_id, dst.floodlight_impressions, dst.date, fa.name, dst.date_last_update, a.id
20. 24.899 61.175 ↑ 1.2 16,862 1

Sort (cost=10,359.57..10,408.65 rows=19,631 width=60) (actual time=55.951..61.175 rows=16,862 loops=1)

  • Sort Key: dst.floodlight_activity_id, dst.floodlight_impressions, dst.date, dst.date_last_update, a.id
  • Sort Method: quicksort Memory: 3140kB
21. 20.642 36.276 ↑ 1.2 16,862 1

Hash Join (cost=286.54..8,959.80 rows=19,631 width=60) (actual time=0.938..36.276 rows=16,862 loops=1)

  • Hash Cond: (dst.floodlight_activity_id = afa.floodlight_activity_id)
22. 14.720 14.720 ↓ 1.0 59,539 1

Seq Scan on dsp_stats_tracking dst (cost=0.00..6,490.50 rows=59,535 width=28) (actual time=0.012..14.720 rows=59,539 loops=1)

23. 0.083 0.914 ↓ 1.1 207 1

Hash (cost=267.78..267.78 rows=183 width=48) (actual time=0.913..0.914 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
24. 0.130 0.831 ↓ 1.1 207 1

Hash Join (cost=218.41..267.78 rows=183 width=48) (actual time=0.647..0.831 rows=207 loops=1)

  • Hash Cond: (a.id = afa.advertiser_id)
25. 0.066 0.066 ↓ 1.0 235 1

Seq Scan on advertiser a (cost=0.00..30.20 rows=232 width=21) (actual time=0.005..0.066 rows=235 loops=1)

26. 0.055 0.635 ↓ 1.1 207 1

Hash (cost=199.66..199.66 rows=183 width=35) (actual time=0.635..0.635 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
27. 0.245 0.580 ↓ 1.1 207 1

Hash Join (cost=119.67..199.66 rows=183 width=35) (actual time=0.209..0.580 rows=207 loops=1)

  • Hash Cond: (afa.floodlight_activity_id = fa.id)
28. 0.141 0.141 ↓ 1.1 625 1

Seq Scan on advertiser_floodlight_activity afa (cost=0.00..59.60 rows=556 width=16) (actual time=0.009..0.141 rows=625 loops=1)

29. 0.065 0.194 ↑ 1.3 207 1

Hash (cost=91.48..91.48 rows=275 width=19) (actual time=0.194..0.194 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
30. 0.129 0.129 ↑ 1.3 207 1

Seq Scan on floodlight_activity fa (cost=0.00..91.48 rows=275 width=19) (actual time=0.011..0.129 rows=207 loops=1)

  • Filter: ((name)::text = 'Conversion'::text)
  • Rows Removed by Filter: 418
31. 180.943 180.943 ↑ 3.9 25,004 1

CTE Scan on dcm (cost=0.00..19,470.20 rows=97,351 width=16) (actual time=149.062..180.943 rows=25,004 loops=1)

32. 0.027 6,622.075 ↓ 3.3 30 1

Hash (cost=54,699.88..54,699.88 rows=9 width=24) (actual time=6,622.075..6,622.075 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
33. 10.462 6,622.048 ↓ 3.3 30 1

Hash Right Join (cost=34,733.02..54,699.88 rows=9 width=24) (actual time=6,565.659..6,622.048 rows=30 loops=1)

  • Hash Cond: ((aff.advertiser_id = dbm.advertiser_id) AND (aff.day = dbm.day))
34. 203.873 203.873 ↑ 2.4 39,715 1

CTE Scan on aff (cost=0.00..19,244.20 rows=96,221 width=24) (actual time=152.934..203.873 rows=39,715 loops=1)

35. 0.017 6,407.713 ↓ 7.5 30 1

Hash (cost=34,732.60..34,732.60 rows=4 width=16) (actual time=6,407.713..6,407.713 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.024 6,407.696 ↓ 7.5 30 1

Merge Left Join (cost=34,703.64..34,732.60 rows=4 width=16) (actual time=6,407.576..6,407.696 rows=30 loops=1)

  • Merge Cond: (dbm.advertiser_id = adv_cancelation_rate.advertiser_id)
37. 0.080 6,327.797 ↓ 7.5 30 1

Merge Join (cost=34,655.99..34,683.55 rows=4 width=16) (actual time=6,327.702..6,327.797 rows=30 loops=1)

  • Merge Cond: (dbm.advertiser_id = adv.id)
38. 0.446 6,327.631 ↑ 3.1 271 1

Merge Left Join (cost=34,625.20..34,650.26 rows=831 width=16) (actual time=6,326.894..6,327.631 rows=271 loops=1)

  • Merge Cond: ((dbm.advertiser_id = fid.advertiser_id) AND (dbm.day = fid.floodlight_day))
39. 2.348 6,218.845 ↑ 3.1 271 1

Sort (cost=34,125.24..34,127.32 rows=831 width=16) (actual time=6,218.763..6,218.845 rows=271 loops=1)

  • Sort Key: dbm.advertiser_id, dbm.day
  • Sort Method: quicksort Memory: 385kB
40. 6,216.497 6,216.497 ↓ 4.9 4,097 1

CTE Scan on dbm (cost=0.00..34,084.94 rows=831 width=16) (actual time=6,131.256..6,216.497 rows=4,097 loops=1)

  • Filter: ((day >= '2019-04-01 00:00:00'::timestamp without time zone) AND (day <= '2019-04-30 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 78230
41. 10.050 108.340 ↑ 1.8 1,096 1

Sort (cost=499.96..504.87 rows=1,963 width=16) (actual time=108.075..108.340 rows=1,096 loops=1)

  • Sort Key: fid.advertiser_id, fid.floodlight_day
  • Sort Method: quicksort Memory: 1559kB
42. 98.290 98.290 ↓ 8.6 16,862 1

CTE Scan on adv_floodlight_interaction fid (cost=0.00..392.60 rows=1,963 width=16) (actual time=79.009..98.290 rows=16,862 loops=1)

43. 0.012 0.086 ↑ 1.0 1 1

Materialize (cost=30.79..30.80 rows=1 width=8) (actual time=0.078..0.086 rows=1 loops=1)

44. 0.013 0.074 ↑ 1.0 1 1

Sort (cost=30.79..30.80 rows=1 width=8) (actual time=0.073..0.074 rows=1 loops=1)

  • Sort Key: adv.id
  • Sort Method: quicksort Memory: 25kB
45. 0.061 0.061 ↑ 1.0 1 1

Seq Scan on advertiser adv (cost=0.00..30.78 rows=1 width=8) (actual time=0.025..0.061 rows=1 loops=1)

  • Filter: ((advertiser_name)::text ~~ '%Zafiro%'::text)
  • Rows Removed by Filter: 234
46. 0.043 79.875 ↑ 5.1 39 1

Sort (cost=47.64..48.14 rows=200 width=8) (actual time=79.867..79.875 rows=39 loops=1)

  • Sort Key: adv_cancelation_rate.advertiser_id
  • Sort Method: quicksort Memory: 30kB
47. 79.832 79.832 ↑ 1.6 123 1

CTE Scan on adv_cancelation_rate (cost=0.00..40.00 rows=200 width=8) (actual time=77.467..79.832 rows=123 loops=1)