explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vm4E : Optimization for: Optimization for: plan #TX9m; plan #SOtU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.047 73,015.615 ↑ 3.8 53 1

Sort (cost=21,152,868.68..21,152,869.18 rows=200 width=64) (actual time=73,015.607..73,015.615 rows=53 loops=1)

  • Sort Key: t.time_stamp
  • Sort Method: quicksort Memory: 32kB
2. 48.736 73,015.568 ↑ 3.8 53 1

HashAggregate (cost=21,152,858.04..21,152,861.04 rows=200 width=64) (actual time=73,015.494..73,015.568 rows=53 loops=1)

  • Group Key: t.time_stamp
3. 1,935.155 72,966.832 ↑ 1,170.3 60,796 1

Nested Loop Left Join (cost=49,433.58..20,441,392.48 rows=71,146,556 width=20) (actual time=55,925.642..72,966.832 rows=60,796 loops=1)

  • Join Filter: ((arl.time_stamp >= t.time_stamp) AND (arl.time_stamp < (t.time_stamp + ('1 week'::cstring)::interval)))
  • Rows Removed by Join Filter: 5,463,288
4. 0.070 0.070 ↑ 18.9 53 1

Function Scan on generate_series t (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.023..0.070 rows=53 loops=1)

5. 1,453.850 71,031.607 ↑ 6.1 104,228 53

Materialize (cost=49,433.58..686,760.27 rows=640,319 width=20) (actual time=1,051.859..1,340.219 rows=104,228 loops=53)

6. 66,870.338 69,577.757 ↑ 6.1 104,228 1

Bitmap Heap Scan on arl (cost=49,433.58..679,806.67 rows=640,319 width=20) (actual time=55,748.347..69,577.757 rows=104,228 loops=1)

  • Recheck Cond: ((brand)::text = 'british-gas'::text)
  • Rows Removed by Index Recheck: 1,625,095
  • Filter: ((period)::text = 'week'::text)
  • Rows Removed by Filter: 46,082
  • Heap Blocks: exact=39,913 lossy=526,829
7. 2,707.419 2,707.419 ↓ 2.9 2,442,061 1

Bitmap Index Scan on arl_brand_index (cost=0.00..49,273.50 rows=852,925 width=0) (actual time=2,707.419..2,707.419 rows=2,442,061 loops=1)

  • Index Cond: ((brand)::text = 'british-gas'::text)
Planning time : 2.871 ms
Execution time : 73,016.849 ms