explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b6iE

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 305,145.626 ↑ 23.8 70 1

Nested Loop (cost=9,852,584.39..11,928,108.25 rows=1,664 width=72) (actual time=71,748.493..305,145.626 rows=70 loops=1)

2.          

CTE leads

3. 6,201.906 66,093.274 ↓ 113.1 5,645,407 1

Nested Loop Left Join (cost=13.49..9,849,270.53 rows=49,910 width=43) (actual time=409.347..66,093.274 rows=5,645,407 loops=1)

4. 9,901.560 48,600.554 ↓ 113.1 5,645,407 1

Nested Loop (cost=13.06..9,579,688.74 rows=49,910 width=22) (actual time=409.322..48,600.554 rows=5,645,407 loops=1)

5. 749.483 24,379.318 ↓ 89.2 7,159,838 1

Append (cost=12.50..9,000,019.11 rows=80,250 width=22) (actual time=409.275..24,379.318 rows=7,159,838 loops=1)

  • Subplans Removed: 7
6. 23,233.119 23,629.790 ↓ 89.2 7,159,838 1

Bitmap Heap Scan on facts_distribution_test_2020_07_01 (cost=123,107.52..8,999,474.37 rows=80,242 width=22) (actual time=409.274..23,629.790 rows=7,159,838 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: 2,030
  • 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=63,026 lossy=65,929
7. 0.005 396.671 ↓ 0.0 0 1

BitmapOr (cost=123,107.52..123,107.52 rows=5,772,559 width=0) (actual time=396.670..396.671 rows=0 loops=1)

8. 121.309 121.309 ↓ 3.2 2,424,726 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..16,140.73 rows=757,215 width=0) (actual time=121.309..121.309 rows=2,424,726 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. 155.460 155.460 ↓ 1.0 2,979,728 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..63,210.57 rows=2,965,398 width=0) (actual time=155.460..155.460 rows=2,979,728 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. 119.897 119.897 ↓ 1.0 2,063,689 1

Bitmap Index Scan on facts_distribution_test_2020_07_01_created_at_idx (cost=0.00..43,696.04 rows=2,049,945 width=0) (actual time=119.897..119.897 rows=2,063,689 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.003 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.003 0.042 ↓ 0.0 0 1

BitmapOr (cost=12.50..12.50 rows=2 width=0) (actual time=0.042..0.042 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.016 0.016 ↓ 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.016..0.016 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. 14,319.676 14,319.676 ↑ 1.0 1 7,159,838

Index Scan using dim_lead_unique on dim_lead dl (cost=0.56..7.22 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=7,159,838)

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

Index Scan using dim_person_pkey on dim_person dp (cost=0.43..5.39 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=5,645,407)

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

CTE revenue

19. 1.337 70,949.486 ↑ 71.3 70 1

WindowAgg (cost=1,729.01..2,065.90 rows=4,991 width=60) (actual time=70,948.103..70,949.486 rows=70 loops=1)

20. 0.101 70,948.149 ↑ 71.3 70 1

Sort (cost=1,729.01..1,741.49 rows=4,991 width=20) (actual time=70,948.081..70,948.149 rows=70 loops=1)

  • Sort Key: l.date, l.hour
  • Sort Method: quicksort Memory: 30kB
21. 1,816.292 70,948.048 ↑ 71.3 70 1

HashAggregate (cost=1,372.53..1,422.44 rows=4,991 width=20) (actual time=70,948.005..70,948.048 rows=70 loops=1)

  • Group Key: l.date, l.hour
22. 69,131.756 69,131.756 ↓ 113.1 5,645,407 1

CTE Scan on leads l (cost=0.00..998.20 rows=49,910 width=20) (actual time=409.350..69,131.756 rows=5,645,407 loops=1)

23. 70,949.754 70,949.754 ↑ 23.8 70 1

CTE Scan on revenue r (cost=0.00..112.30 rows=1,664 width=60) (actual time=70,948.107..70,949.754 rows=70 loops=1)

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

Limit (cost=1,247.96..1,247.97 rows=1 width=8) (actual time=3,345.648..3,345.650 rows=1 loops=70)

25. 177,867.060 234,195.220 ↑ 1.0 1 70

Aggregate (cost=1,247.96..1,247.97 rows=1 width=8) (actual time=3,345.646..3,345.646 rows=1 loops=70)

26. 56,328.160 56,328.160 ↓ 10,785.9 895,229 70

CTE Scan on leads s (cost=0.00..1,247.75 rows=83 width=516) (actual time=226.758..804.688 rows=895,229 loops=70)

  • Filter: ((hour <= r."Hour") AND (date = r.raw_date))
  • Rows Removed by Filter: 4,750,178
Planning time : 4.042 ms
Execution time : 305,196.584 ms