explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xOCQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 7,267.839 ↑ 1.0 1 1

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

2. 7.533 7,267.816 ↓ 1.4 30 1

Hash Right Join (cost=1,202,712.81..1,222,915.34 rows=22 width=632) (actual time=7,228.793..7,267.816 rows=30 loops=1)

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

CTE aff

4. 118.036 185.512 ↑ 2.4 39,715 1

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

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

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

6.          

CTE dbm

7. 4,016.944 6,594.351 ↑ 2.0 82,327 1

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

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

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

9.          

CTE dcm

10. 98.607 174.973 ↑ 3.9 25,004 1

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

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

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

12.          

CTE adv_cancelation_rate

13. 0.176 86.722 ↑ 1.6 123 1

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

  • Group Key: advertiser_sales_1.advertiser_id
14. 1.396 86.546 ↑ 1.9 280 1

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

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

Sort (cost=18,203.24..18,204.61 rows=547 width=26) (actual time=84.292..85.150 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. 82.565 82.565 ↓ 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.080..82.565 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: 107668
17.          

CTE adv_floodlight_interaction

18. 18.150 93.292 ↓ 8.6 16,862 1

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

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

Group (cost=10,359.57..10,703.11 rows=19,631 width=576) (actual time=58.751..75.142 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. 26.633 65.239 ↑ 1.2 16,862 1

Sort (cost=10,359.57..10,408.65 rows=19,631 width=60) (actual time=58.749..65.239 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. 22.067 38.606 ↑ 1.2 16,862 1

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

  • Hash Cond: (dst.floodlight_activity_id = afa.floodlight_activity_id)
22. 15.568 15.568 ↓ 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..15.568 rows=59,539 loops=1)

23. 0.083 0.971 ↓ 1.1 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
24. 0.135 0.888 ↓ 1.1 207 1

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

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

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

26. 0.073 0.685 ↓ 1.1 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
27. 0.243 0.612 ↓ 1.1 207 1

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

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

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

29. 0.081 0.217 ↑ 1.3 207 1

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

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

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

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

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

32. 0.038 7,068.277 ↓ 3.3 30 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
33. 11.429 7,068.239 ↓ 3.3 30 1

Hash Right Join (cost=34,733.02..54,699.88 rows=9 width=24) (actual time=7,008.356..7,068.239 rows=30 loops=1)

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

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

35. 0.011 6,842.967 ↓ 7.5 30 1

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

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

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

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

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

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

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

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

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

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

CTE Scan on dbm (cost=0.00..34,084.94 rows=831 width=16) (actual time=6,545.015..6,637.110 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. 11.029 115.757 ↑ 1.8 1,096 1

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

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

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

43. 0.009 0.068 ↑ 1.0 1 1

Materialize (cost=30.79..30.80 rows=1 width=8) (actual time=0.061..0.068 rows=1 loops=1)

44. 0.010 0.059 ↑ 1.0 1 1

Sort (cost=30.79..30.80 rows=1 width=8) (actual time=0.058..0.059 rows=1 loops=1)

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

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

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

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

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

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

Planning time : 1.681 ms
Execution time : 7,270.930 ms