explain.depesz.com

PostgreSQL's explain analyze made readable

Result: plrI

Settings
# exclusive inclusive rows x rows loops node
1. 0.387 123,000.107 ↑ 182.6 73 1

Nested Loop (cost=3,865,455.04..963,091,756.28 rows=13,333 width=72) (actual time=33,405.009..123,000.107 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=775,308, temp read=1,744,269 written=432,524
2.          

CTE distributions

3. 839.569 7,043.155 ↑ 1.4 3,254,054 1

Unique (cost=1,102,689.21..1,180,367.03 rows=4,438,733 width=34) (actual time=5,710.454..7,043.155 rows=3,254,054 loops=1)

  • Output: facts_distribution_test_2020_07_01.lead_id, facts_distribution_test_2020_07_01.person_id, facts_distribution_test_2020_07_01.accepted, facts_distribution_test_2020_07_01.date, facts_distribution_test_2020_07_01.""time"", facts_distribution_test_2020_07_01.created_at
  • Buffers: shared hit=42,799, temp read=67,017 written=67,128
4. 3,511.811 6,203.586 ↑ 1.0 4,316,862 1

Sort (cost=1,102,689.21..1,113,786.04 rows=4,438,733 width=34) (actual time=5,710.452..6,203.586 rows=4,316,862 loops=1)

  • Output: facts_distribution_test_2020_07_01.lead_id, facts_distribution_test_2020_07_01.person_id, facts_distribution_test_2020_07_01.accepted, facts_distribution_test_2020_07_01.date, facts_distribution_test_2020_07_01.""time"", facts_distribution_test_2020_07_01.created_at
  • Sort Key: facts_distribution_test_2020_07_01.lead_id, facts_distribution_test_2020_07_01.person_id, facts_distribution_test_2020_07_01.accepted, facts_distribution_test_2020_07_01.date, facts_distribution_test_2020_07_01.""time"", facts_distribution_test_2020_07_01.created_at
  • Sort Method: external merge Disk: 211,208kB
  • Buffers: shared hit=42,799, temp read=67,017 written=67,128
5. 385.817 2,691.775 ↑ 1.0 4,316,862 1

Append (cost=0.56..369,868.99 rows=4,438,733 width=34) (actual time=0.041..2,691.775 rows=4,316,862 loops=1)

  • Buffers: shared hit=42,799
6. 161.165 850.199 ↑ 1.1 1,594,573 1

Append (cost=0.56..115,886.67 rows=1,705,177 width=34) (actual time=0.041..850.199 rows=1,594,573 loops=1)

  • Buffers: shared hit=13,702
7. 689.034 689.034 ↑ 1.1 1,594,573 1

Index Scan using facts_distribution_test_2020_07_01_created_at_idx on public.facts_distribution_test_2020_07_01 (cost=0.56..107,360.79 rows=1,705,177 width=34) (actual time=0.041..689.034 rows=1,594,573 loops=1)

  • Output: facts_distribution_test_2020_07_01.lead_id, facts_distribution_test_2020_07_01.person_id, facts_distribution_test_2020_07_01.accepted, facts_distribution_test_2020_07_01.date, facts_distribution_test_2020_07_01.""time"", facts_distribution_test_2020_07_01.created_at
  • Index Cond: ((facts_distribution_test_2020_07_01.created_at >= '2020-07-15'::date) AND (facts_distribution_test_2020_07_01.created_at <= '2020-07-16 00:00:00'::timestamp without time zone))
  • Filter: ((facts_distribution_test_2020_07_01.type)::text = 'post'::text)
  • Rows Removed by Filter: 246,387
  • Buffers: shared hit=13,702
8. 137.347 730.164 ↑ 1.1 1,368,460 1

Append (cost=0.56..98,591.35 rows=1,441,018 width=34) (actual time=0.039..730.164 rows=1,368,460 loops=1)

  • Buffers: shared hit=14,030
9. 592.817 592.817 ↑ 1.1 1,368,460 1

Index Scan using facts_distribution_test_2020_07_01_created_at_idx on public.facts_distribution_test_2020_07_01 facts_distribution_test_2020_07_01_1 (cost=0.56..91,386.26 rows=1,441,018 width=34) (actual time=0.039..592.817 rows=1,368,460 loops=1)

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

Append (cost=0.56..88,809.97 rows=1,292,538 width=34) (actual time=0.053..725.595 rows=1,353,829 loops=1)

  • Buffers: shared hit=15,067
11. 590.369 590.369 ↓ 1.0 1,353,829 1

Index Scan using facts_distribution_test_2020_07_01_created_at_idx on public.facts_distribution_test_2020_07_01 facts_distribution_test_2020_07_01_2 (cost=0.56..82,347.28 rows=1,292,538 width=34) (actual time=0.053..590.369 rows=1,353,829 loops=1)

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

CTE leads

13. 3,111.117 31,709.223 ↑ 1.1 2,635,836 1

Hash Left Join (cost=2,080,132.77..2,529,150.62 rows=2,877,483 width=43) (actual time=23,760.921..31,709.223 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=775,308, temp read=288,871 written=308,841
14. 2,915.870 21,293.643 ↑ 1.1 2,635,836 1

Hash Join (cost=1,394,081.79..1,653,640.12 rows=2,877,483 width=22) (actual time=16,136.952..21,293.643 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=632,564, temp read=166,313 written=186,283
15. 7,953.344 7,953.344 ↑ 1.4 3,254,054 1

CTE Scan on distributions fd (cost=0.00..88,774.66 rows=4,438,733 width=22) (actual time=5,710.459..7,953.344 rows=3,254,054 loops=1)

  • Output: fd.created_at, fd.accepted, fd.lead_id, fd.person_id
  • Buffers: shared hit=42,799, temp read=67,017 written=86,987
16. 3,800.639 10,424.429 ↓ 1.0 22,143,614 1

Hash (cost=1,012,756.50..1,012,756.50 rows=21,936,903 width=16) (actual time=10,424.429..10,424.429 rows=22,143,614 loops=1)

  • Output: dl.revenue, dl.source_id
  • Buckets: 131,072 Batches: 512 Memory Usage: 2,778kB
  • Buffers: shared hit=589,765, temp written=79,500
17. 6,623.790 6,623.790 ↓ 1.0 22,143,614 1

Seq Scan on public.dim_lead dl (cost=0.00..1,012,756.50 rows=21,936,903 width=16) (actual time=0.016..6,623.790 rows=22,143,614 loops=1)

  • Output: dl.revenue, dl.source_id
  • Filter: (dl.vertical = 6)
  • Rows Removed by Filter: 11,798,976
  • Buffers: shared hit=589,765
18. 3,953.825 7,304.463 ↑ 1.0 18,513,658 1

Hash (cost=327,945.77..327,945.77 rows=18,520,177 width=27) (actual time=7,304.462..7,304.463 rows=18,513,658 loops=1)

  • Output: dp.email, dp.id
  • Buckets: 65,536 Batches: 512 Memory Usage: 2,646kB
  • Buffers: shared hit=142,744, temp written=110,230
19. 3,350.638 3,350.638 ↑ 1.0 18,513,658 1

Seq Scan on public.dim_person dp (cost=0.00..327,945.77 rows=18,520,177 width=27) (actual time=0.007..3,350.638 rows=18,513,658 loops=1)

  • Output: dp.email, dp.id
  • Buffers: shared hit=142,744
20.          

CTE revenue

21. 0.744 33,122.148 ↑ 547.9 73 1

WindowAgg (cost=82,588.32..83,988.32 rows=40,000 width=60) (actual time=33,121.337..33,122.148 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=775,308, temp read=288,871 written=327,343
22. 0.109 33,121.404 ↑ 547.9 73 1

Sort (cost=82,588.32..82,688.32 rows=40,000 width=20) (actual time=33,121.325..33,121.404 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=775,308, temp read=288,871 written=327,343
23. 551.265 33,121.295 ↑ 547.9 73 1

HashAggregate (cost=79,130.78..79,530.78 rows=40,000 width=20) (actual time=33,121.069..33,121.295 rows=73 loops=1)

  • Output: l.date, l.hour, sum(l.revenue)
  • Group Key: l.date, l.hour
  • Buffers: shared hit=775,308, temp read=288,871 written=327,343
24. 32,570.030 32,570.030 ↑ 1.1 2,635,836 1

CTE Scan on leads l (cost=0.00..57,549.66 rows=2,877,483 width=20) (actual time=23,760.923..32,570.030 rows=2,635,836 loops=1)

  • Output: l.date, l.hour, l.revenue, l.people
  • Buffers: shared hit=775,308, temp read=288,871 written=327,343
25. 33,122.412 33,122.412 ↑ 182.6 73 1

CTE Scan on revenue r (cost=0.00..900.00 rows=13,333 width=60) (actual time=33,121.340..33,122.412 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=775,308, temp read=288,871 written=327,343
26. 0.365 89,877.308 ↑ 1.0 1 73

Limit (cost=71,949.07..71,949.07 rows=1 width=8) (actual time=1,231.194..1,231.196 rows=1 loops=73)

  • Output: (count(DISTINCT s.people))
  • Buffers: temp read=1,455,398 written=105,181
27. 67,615.009 89,876.943 ↑ 1.0 1 73

Aggregate (cost=71,949.07..71,949.07 rows=1 width=8) (actual time=1,231.191..1,231.191 rows=1 loops=73)

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

CTE Scan on leads s (cost=0.00..71,937.07 rows=4,796 width=516) (actual time=1.116..304.958 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 : 1.585 ms
Execution time : 123,076.592 ms