explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 05mb

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 6,851.691 ↑ 1.0 1 1

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

2. 6.961 6,851.665 ↓ 1.4 30 1

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

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

CTE aff

4. 111.531 176.123 ↑ 2.4 39,715 1

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

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

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

6.          

CTE dbm

7. 3,785.712 6,211.983 ↑ 2.0 82,327 1

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

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

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

9.          

CTE dcm

10. 93.557 165.650 ↑ 3.9 25,004 1

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

  • Group Key: dsp_conversion.advertiser_id, date_trunc('day'::text, dsp_conversion.interaction_date_time)
11. 72.093 72.093 ↓ 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..72.093 rows=99,879 loops=1)

12.          

CTE adv_cancelation_rate

13. 0.160 84.367 ↑ 1.6 123 1

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

  • Group Key: advertiser_sales_1.advertiser_id
14. 1.403 84.207 ↑ 1.9 280 1

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

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

Sort (cost=18,203.24..18,204.61 rows=547 width=26) (actual time=81.981..82.804 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. 80.371 80.371 ↓ 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..80.371 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. 16.672 87.928 ↓ 8.6 16,862 1

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

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

Group (cost=10,359.57..10,703.11 rows=19,631 width=576) (actual time=55.346..71.256 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. 25.407 61.777 ↑ 1.2 16,862 1

Sort (cost=10,359.57..10,408.65 rows=19,631 width=60) (actual time=55.342..61.777 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.928 36.370 ↑ 1.2 16,862 1

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

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

23. 0.076 0.918 ↓ 1.1 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
24. 0.139 0.842 ↓ 1.1 207 1

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

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

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

26. 0.071 0.642 ↓ 1.1 207 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
27. 0.228 0.571 ↓ 1.1 207 1

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

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

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

29. 0.070 0.199 ↑ 1.3 207 1

Hash (cost=91.48..91.48 rows=275 width=19) (actual time=0.198..0.199 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.010..0.129 rows=207 loops=1)

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

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

32. 0.029 6,663.007 ↓ 3.3 30 1

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

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

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

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

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

35. 0.015 6,449.437 ↓ 7.5 30 1

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Sort Key: fid.advertiser_id, fid.floodlight_day
  • Sort Method: quicksort Memory: 1559kB
42. 98.625 98.625 ↓ 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.623..98.625 rows=16,862 loops=1)

43. 0.012 0.071 ↑ 1.0 1 1

Materialize (cost=30.79..30.80 rows=1 width=8) (actual time=0.062..0.071 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.050 84.507 ↑ 5.1 39 1

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

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

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