explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5QOu

Settings
# exclusive inclusive rows x rows loops node
1. 0.443 369,280.224 ↑ 23.5 70 1

Nested Loop (cost=9,729,742.84..11,762,976.76 rows=1,647 width=72) (actual time=98,008.041..369,280.224 rows=70 loops=1)

2.          

CTE leads

3. 6,432.724 92,217.002 ↓ 115.1 5,684,338 1

Nested Loop Left Join (cost=13.37..9,726,463.31 rows=49,398 width=43) (actual time=405.652..92,217.002 rows=5,684,338 loops=1)

4. 6,490.492 74,415.602 ↓ 115.1 5,684,338 1

Nested Loop (cost=12.93..9,502,648.37 rows=49,398 width=22) (actual time=405.613..74,415.602 rows=5,684,338 loops=1)

5. 790.914 24,610.774 ↓ 89.9 7,219,056 1

Append (cost=12.50..8,986,067.35 rows=80,282 width=22) (actual time=401.576..24,610.774 rows=7,219,056 loops=1)

  • Subplans Removed: 7
6. 23,430.860 23,819.815 ↓ 89.9 7,219,056 1

Bitmap Heap Scan on facts_distribution_test_2020_07_01 (cost=121,158.88..8,985,522.46 rows=80,274 width=22) (actual time=401.575..23,819.815 rows=7,219,056 loops=1)

  • Recheck Cond: (((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '13 days'::interval) - '00:01:00'::interval))))
  • Rows Removed by Index Recheck: 208
  • Filter: ((((created_at)::date = (timezone('Canada/Eastern'::text, now()))::date) OR ((created_at)::date = ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) OR ((created_at)::date = ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval))) AND (((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '13 days'::interval) - '00:01:00'::interval)) AND ((type)::text = 'post'::text))))
  • Rows Removed by Filter: 237,219
  • Heap Blocks: exact=62,456 lossy=65,928
7. 0.004 388.955 ↓ 0.0 0 1

BitmapOr (cost=121,158.88..121,158.88 rows=5,774,893 width=0) (actual time=388.955..388.955 rows=0 loops=1)

8. 122.618 122.618 ↓ 3.3 2,483,913 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..15,887.79 rows=757,521 width=0) (actual time=122.618..122.618 rows=2,483,913 loops=1)

  • Index Cond: ((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval)))
9. 144.696 144.696 ↑ 1.0 2,932,169 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..62,206.56 rows=2,966,597 width=0) (actual time=144.696..144.696 rows=2,932,169 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval)))
10. 121.637 121.637 ↑ 1.0 2,040,594 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..43,004.33 rows=2,050,774 width=0) (actual time=121.637..121.637 rows=2,040,594 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '13 days'::interval) - '00:01:00'::interval)))
11. 0.007 0.045 ↓ 0.0 0 1

Bitmap Heap Scan on facts_distribution_test_2020_08_01 (cost=12.50..18.08 rows=1 width=22) (actual time=0.045..0.045 rows=0 loops=1)

  • Recheck Cond: (((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval))) OR ((type)::text = 'post'::text))
  • Filter: ((((created_at)::date = (timezone('Canada/Eastern'::text, now()))::date) OR ((created_at)::date = ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) OR ((created_at)::date = ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval))) AND (((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval))) OR ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '14 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '13 days'::interval) - '00:01:00'::interval)) AND ((type)::text = 'post'::text))))
12. 0.001 0.038 ↓ 0.0 0 1

BitmapOr (cost=12.50..12.50 rows=2 width=0) (actual time=0.038..0.038 rows=0 loops=1)

13. 0.020 0.020 ↓ 0.0 0 1

Bitmap Index Scan on facts_distribution_test_2020_08_01_created_at_idx (cost=0.00..4.17 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: ((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval)))
14. 0.014 0.014 ↓ 0.0 0 1

Bitmap Index Scan on facts_distribution_test_2020_08_01_created_at_idx (cost=0.00..4.17 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= (((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval) - '00:01:00'::interval)))
15. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on facts_distribution_test_2020_08_01_type_idx (cost=0.00..4.15 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((type)::text = 'post'::text)
16. 43,314.336 43,314.336 ↑ 1.0 1 7,219,056

Index Scan using dim_lead_unique on dim_lead dl (cost=0.44..6.43 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=7,219,056)

  • Index Cond: (source_id = facts_distribution_test_2020_07_01.lead_id)
  • Filter: (vertical = 6)
  • Rows Removed by Filter: 0
17. 11,368.676 11,368.676 ↑ 1.0 1 5,684,338

Index Scan using dim_person_pkey on dim_person dp (cost=0.43..4.52 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=5,684,338)

  • Index Cond: (facts_distribution_test_2020_07_01.person_id = id)
18.          

CTE revenue

19. 1.567 97,190.409 ↑ 70.6 70 1

WindowAgg (cost=1,710.92..2,044.37 rows=4,940 width=60) (actual time=97,188.792..97,190.409 rows=70 loops=1)

20. 0.099 97,188.842 ↑ 70.6 70 1

Sort (cost=1,710.92..1,723.27 rows=4,940 width=20) (actual time=97,188.772..97,188.842 rows=70 loops=1)

  • Sort Key: l.date, l.hour
  • Sort Method: quicksort Memory: 30kB
21. 1,923.036 97,188.743 ↑ 70.6 70 1

HashAggregate (cost=1,358.44..1,407.85 rows=4,940 width=20) (actual time=97,188.701..97,188.743 rows=70 loops=1)

  • Group Key: l.date, l.hour
22. 95,265.707 95,265.707 ↓ 115.1 5,684,338 1

CTE Scan on leads l (cost=0.00..987.96 rows=49,398 width=20) (actual time=405.657..95,265.707 rows=5,684,338 loops=1)

23. 97,190.691 97,190.691 ↑ 23.5 70 1

CTE Scan on revenue r (cost=0.00..111.15 rows=1,647 width=60) (actual time=97,188.795..97,190.691 rows=70 loops=1)

  • Filter: ("Hour" >= '0'::double precision)
24. 0.280 272,089.090 ↑ 1.0 1 70

Limit (cost=1,235.15..1,235.16 rows=1 width=8) (actual time=3,886.985..3,886.987 rows=1 loops=70)

25. 205,544.640 272,088.810 ↑ 1.0 1 70

Aggregate (cost=1,235.15..1,235.16 rows=1 width=8) (actual time=3,886.983..3,886.983 rows=1 loops=70)

26. 66,544.170 66,544.170 ↓ 10,924.2 895,785 70

CTE Scan on leads s (cost=0.00..1,234.95 rows=82 width=516) (actual time=250.207..950.631 rows=895,785 loops=70)

  • Filter: ((hour <= r."Hour") AND (date = r.raw_date))
  • Rows Removed by Filter: 4,788,553
Planning time : 18.268 ms
Execution time : 369,343.367 ms