explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ylqW

Settings
# exclusive inclusive rows x rows loops node
1. 0.425 119,674.283 ↑ 182.6 73 1

Nested Loop (cost=3,692,629.95..934,550,035.03 rows=13,333 width=72) (actual time=31,081.538..119,674.283 rows=73 loops=1)

  • Output: r.""Days Ago"", r.""Date"", r.""Hour"", r.""Total Revenue"", (count(DISTINCT s.people)), (r.""Total Revenue"" / ((count(DISTINCT s.people)))::double precision)
  • Buffers: shared hit=906,851, temp read=1,732,114 written=420,369
2.          

CTE distributions

3. 805.961 6,914.880 ↑ 1.3 3,254,054 1

Unique (cost=1,051,869.58..1,127,250.11 rows=4,307,459 width=34) (actual time=5,612.411..6,914.880 rows=3,254,054 loops=1)

  • Output: facts_distribution_2020_07_01_30.lead_id, facts_distribution_2020_07_01_30.person_id, facts_distribution_2020_07_01_30.accepted, facts_distribution_2020_07_01_30.date, facts_distribution_2020_07_01_30.""time"", facts_distribution_2020_07_01_30.created_at
  • Buffers: shared hit=184,856, temp read=67,017 written=67,128
4. 3,406.192 6,108.919 ↓ 1.0 4,316,862 1

Sort (cost=1,051,869.58..1,062,638.23 rows=4,307,459 width=34) (actual time=5,612.409..6,108.919 rows=4,316,862 loops=1)

  • Output: facts_distribution_2020_07_01_30.lead_id, facts_distribution_2020_07_01_30.person_id, facts_distribution_2020_07_01_30.accepted, facts_distribution_2020_07_01_30.date, facts_distribution_2020_07_01_30.""time"", facts_distribution_2020_07_01_30.created_at
  • Sort Key: facts_distribution_2020_07_01_30.lead_id, facts_distribution_2020_07_01_30.person_id, facts_distribution_2020_07_01_30.accepted, facts_distribution_2020_07_01_30.date, facts_distribution_2020_07_01_30.""time"", facts_distribution_2020_07_01_30.created_at
  • Sort Method: external merge Disk: 211,208kB
  • Buffers: shared hit=184,856, temp read=67,017 written=67,128
5. 384.632 2,702.727 ↓ 1.0 4,316,862 1

Append (cost=0.43..341,650.93 rows=4,307,459 width=34) (actual time=0.042..2,702.727 rows=4,316,862 loops=1)

  • Buffers: shared hit=184,856
6. 174.167 856.946 ↓ 1.0 1,594,573 1

Append (cost=0.43..97,988.04 rows=1,570,405 width=34) (actual time=0.041..856.946 rows=1,594,573 loops=1)

  • Buffers: shared hit=64,793
7. 682.779 682.779 ↓ 1.0 1,594,573 1

Index Scan using facts_distribution_2020_07_01_30_created_at_idx on public.facts_distribution_2020_07_01_30 (cost=0.43..90,136.01 rows=1,570,405 width=34) (actual time=0.041..682.779 rows=1,594,573 loops=1)

  • Output: facts_distribution_2020_07_01_30.lead_id, facts_distribution_2020_07_01_30.person_id, facts_distribution_2020_07_01_30.accepted, facts_distribution_2020_07_01_30.date, facts_distribution_2020_07_01_30.""time"", facts_distribution_2020_07_01_30.created_at
  • Index Cond: ((facts_distribution_2020_07_01_30.created_at >= '2020-07-15'::date) AND (facts_distribution_2020_07_01_30.created_at <= '2020-07-16 00:00:00'::timestamp without time zone))
  • Filter: ((facts_distribution_2020_07_01_30.type)::text = 'post'::text)
  • Rows Removed by Filter: 246,387
  • Buffers: shared hit=64,793
8. 134.960 717.962 ↑ 1.0 1,368,460 1

Append (cost=0.43..86,816.22 rows=1,369,279 width=34) (actual time=0.042..717.962 rows=1,368,460 loops=1)

  • Buffers: shared hit=59,410
9. 583.002 583.002 ↑ 1.0 1,368,460 1

Index Scan using facts_distribution_2020_07_01_29_created_at_idx on public.facts_distribution_2020_07_01_29 (cost=0.43..79,969.83 rows=1,369,279 width=34) (actual time=0.041..583.002 rows=1,368,460 loops=1)

  • Output: facts_distribution_2020_07_01_29.lead_id, facts_distribution_2020_07_01_29.person_id, facts_distribution_2020_07_01_29.accepted, facts_distribution_2020_07_01_29.date, facts_distribution_2020_07_01_29.""time"", facts_distribution_2020_07_01_29.created_at
  • Index Cond: ((facts_distribution_2020_07_01_29.created_at >= '2020-07-08 00:00:00'::timestamp without time zone) AND (facts_distribution_2020_07_01_29.created_at <= '2020-07-08 23:59:00'::timestamp without time zone))
  • Filter: ((facts_distribution_2020_07_01_29.type)::text = 'post'::text)
  • Rows Removed by Filter: 232,356
  • Buffers: shared hit=59,410
10. 152.331 743.187 ↑ 1.0 1,353,829 1

Append (cost=0.43..92,234.79 rows=1,367,775 width=34) (actual time=0.049..743.187 rows=1,353,829 loops=1)

  • Buffers: shared hit=60,653
11. 590.856 590.856 ↑ 1.0 1,353,829 1

Index Scan using facts_distribution_2020_07_01_28_created_at_idx on public.facts_distribution_2020_07_01_28 (cost=0.43..85,395.91 rows=1,367,775 width=34) (actual time=0.048..590.856 rows=1,353,829 loops=1)

  • Output: facts_distribution_2020_07_01_28.lead_id, facts_distribution_2020_07_01_28.person_id, facts_distribution_2020_07_01_28.accepted, facts_distribution_2020_07_01_28.date, facts_distribution_2020_07_01_28.""time"", facts_distribution_2020_07_01_28.created_at
  • Index Cond: ((facts_distribution_2020_07_01_28.created_at >= '2020-07-01 00:00:00'::timestamp without time zone) AND (facts_distribution_2020_07_01_28.created_at <= '2020-07-01 23:59:00'::timestamp without time zone))
  • Filter: ((facts_distribution_2020_07_01_28.type)::text = 'post'::text)
  • Rows Removed by Filter: 261,549
  • Buffers: shared hit=60,653
12.          

CTE leads

13. 2,970.482 29,408.316 ↑ 1.1 2,635,836 1

Hash Left Join (cost=1,986,134.48..2,413,910.61 rows=2,792,382 width=43) (actual time=21,694.599..29,408.316 rows=2,635,836 loops=1)

  • Output: (fd.created_at)::date, date_part('hour'::text, fd.created_at), CASE WHEN (fd.accepted = 1) THEN dl.revenue ELSE '0'::double precision END, dp.email
  • Inner Unique: true
  • Hash Cond: (fd.person_id = dp.id)
  • Buffers: shared hit=906,851, temp read=276,716 written=296,686
14. 2,829.587 20,120.893 ↑ 1.1 2,635,836 1

Hash Join (cost=1,348,216.94..1,597,159.20 rows=2,792,382 width=22) (actual time=15,069.354..20,120.893 rows=2,635,836 loops=1)

  • Output: fd.created_at, fd.accepted, fd.person_id, dl.revenue
  • Inner Unique: true
  • Hash Cond: (fd.lead_id = dl.source_id)
  • Buffers: shared hit=774,621, temp read=161,965 written=181,935
15. 7,836.324 7,836.324 ↑ 1.3 3,254,054 1

CTE Scan on distributions fd (cost=0.00..86,149.18 rows=4,307,459 width=22) (actual time=5,612.415..7,836.324 rows=3,254,054 loops=1)

  • Output: fd.created_at, fd.accepted, fd.lead_id, fd.person_id
  • Buffers: shared hit=184,856, temp read=67,017 written=86,987
16. 3,429.024 9,454.982 ↓ 1.0 20,871,470 1

Hash (cost=988,636.10..988,636.10 rows=20,685,987 width=16) (actual time=9,454.982..9,454.982 rows=20,871,470 loops=1)

  • Output: dl.revenue, dl.source_id
  • Buckets: 131,072 Batches: 512 Memory Usage: 2,681kB
  • Buffers: shared hit=589,765, temp written=75,152
17. 6,025.958 6,025.958 ↓ 1.0 20,871,470 1

Seq Scan on public.dim_lead dl (cost=0.00..988,636.10 rows=20,685,987 width=16) (actual time=0.013..6,025.958 rows=20,871,470 loops=1)

  • Output: dl.revenue, dl.source_id
  • Filter: (dl.vertical = 6)
  • Rows Removed by Filter: 11,271,617
  • Buffers: shared hit=589,765
18. 3,504.721 6,316.941 ↑ 1.0 17,207,188 1

Hash (cost=304,608.02..304,608.02 rows=17,237,802 width=27) (actual time=6,316.941..6,316.941 rows=17,207,188 loops=1)

  • Output: dp.email, dp.id
  • Buckets: 65,536 Batches: 512 Memory Usage: 2,494kB
  • Buffers: shared hit=132,230, temp written=102,423
19. 2,812.220 2,812.220 ↑ 1.0 17,207,188 1

Seq Scan on public.dim_person dp (cost=0.00..304,608.02 rows=17,237,802 width=27) (actual time=0.007..2,812.220 rows=17,207,188 loops=1)

  • Output: dp.email, dp.id
  • Buffers: shared hit=132,230
20.          

CTE revenue

21. 0.786 30,799.452 ↑ 547.9 73 1

WindowAgg (cost=80,248.05..81,648.05 rows=40,000 width=60) (actual time=30,798.600..30,799.452 rows=73 loops=1)

  • Output: l.date, date_part('day'::text, ('2020-07-15 08:00:00'::timestamp without time zone - (l.date)::timestamp without time zone)), CASE WHEN (l.date = '2020-07-15'::date) THEN 'Today'::text WHEN (l.date = '2020-07-08 00:00:00'::timestamp without time zone) THEN '1 Week Ago'::text WHEN (l.date = '2020-07-01 00:00:00'::timestamp without time zone) THEN '2 Weeks Ago'::text ELSE NULL::text END, l.hour, sum((sum(l.revenue))) OVER (?)
  • Buffers: shared hit=906,851, temp read=276,716 written=315,188
22. 0.105 30,798.666 ↑ 547.9 73 1

Sort (cost=80,248.05..80,348.05 rows=40,000 width=20) (actual time=30,798.588..30,798.666 rows=73 loops=1)

  • Output: l.date, l.hour, (sum(l.revenue))
  • Sort Key: l.date, l.hour
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=906,851, temp read=276,716 written=315,188
23. 530.651 30,798.561 ↑ 547.9 73 1

HashAggregate (cost=76,790.51..77,190.51 rows=40,000 width=20) (actual time=30,798.326..30,798.561 rows=73 loops=1)

  • Output: l.date, l.hour, sum(l.revenue)
  • Group Key: l.date, l.hour
  • Buffers: shared hit=906,851, temp read=276,716 written=315,188
24. 30,267.910 30,267.910 ↑ 1.1 2,635,836 1

CTE Scan on leads l (cost=0.00..55,847.64 rows=2,792,382 width=20) (actual time=21,694.600..30,267.910 rows=2,635,836 loops=1)

  • Output: l.date, l.hour, l.revenue, l.people
  • Buffers: shared hit=906,851, temp read=276,716 written=315,188
25. 30,799.716 30,799.716 ↑ 182.6 73 1

CTE Scan on revenue r (cost=0.00..900.00 rows=13,333 width=60) (actual time=30,798.603..30,799.716 rows=73 loops=1)

  • Output: r.raw_date, r.""Days Ago"", r.""Date"", r.""Hour"", r.""Total Revenue"
  • Filter: (r."Hour" >= '0'::double precision)
  • Buffers: shared hit=906,851, temp read=276,716 written=315,188
26. 0.292 88,874.142 ↑ 1.0 1 73

Limit (cost=69,821.18..69,821.19 rows=1 width=8) (actual time=1,217.452..1,217.454 rows=1 loops=73)

  • Output: (count(DISTINCT s.people))
  • Buffers: temp read=1,455,398 written=105,181
27. 66,809.016 88,873.850 ↑ 1.0 1 73

Aggregate (cost=69,821.18..69,821.19 rows=1 width=8) (actual time=1,217.450..1,217.450 rows=1 loops=73)

  • Output: count(DISTINCT s.people)
  • Buffers: temp read=1,455,398 written=105,181
28. 22,064.834 22,064.834 ↓ 88.4 411,328 73

CTE Scan on leads s (cost=0.00..69,809.55 rows=4,654 width=516) (actual time=1.319..302.258 rows=411,328 loops=73)

  • Output: s.date, s.hour, s.revenue, s.people
  • Filter: ((s.hour <= r."Hour") AND (s.date = r.raw_date))
  • Rows Removed by Filter: 2,224,508
  • Buffers: temp read=1,350,719 written=1
Planning time : 2.366 ms
Execution time : 119,755.738 ms