explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nNYk

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.114 40,340.447 ↑ 1.0 1 1

Aggregate (cost=1,893.16..1,893.17 rows=1 width=12) (actual time=40,340.447..40,340.447 rows=1 loops=1)

2.          

CTE agg_usage_by_acct

3. 0.089 40,340.156 ↓ 1,000.0 1,000 1

Limit (cost=1,893.11..1,893.14 rows=1 width=8) (actual time=40,338.707..40,340.156 rows=1,000 loops=1)

4. 0.896 40,340.067 ↓ 1,000.0 1,000 1

GroupAggregate (cost=1,893.11..1,893.14 rows=1 width=8) (actual time=40,338.705..40,340.067 rows=1,000 loops=1)

  • Group Key: usage.user_id
  • Filter: ((count(1) > 0) AND (sum(usage.usage) >= '174.999999710648'::double precision))
  • Rows Removed by Filter: 289
5. 10.033 40,339.171 ↓ 5,250.0 5,250 1

Sort (cost=1,893.11..1,893.12 rows=1 width=8) (actual time=40,338.685..40,339.171 rows=5,250 loops=1)

  • Sort Key: usage.user_id
  • Sort Method: quicksort Memory: 1727kB
6. 15.723 40,329.138 ↓ 20,440.0 20,440 1

Nested Loop (cost=28.45..1,893.10 rows=1 width=8) (actual time=117.835..40,329.138 rows=20,440 loops=1)

7. 963.047 963.047 ↓ 8,488.0 8,488 1

Index Scan using premises_location_idx on premises (cost=0.29..8.68 rows=1 width=4) (actual time=7.285..963.047 rows=8,488 loops=1)

  • Index Cond: (location && '0101000020E610000010441CD75DC25BC03DB4279A15D84640'::geography)
  • Filter: (('0101000020E610000010441CD75DC25BC03DB4279A15D84640'::geography && _st_expand(location, '3621.024'::double precision)) AND _st_dwithin(location, '0101000020E610000010441CD75DC25BC03DB4279A15D84640'::geography, '3621.024'::double precision, true))
  • Rows Removed by Filter: 2337
8. 26,465.584 39,350.368 ↑ 2.0 2 8,488

Bitmap Heap Scan on usage_5yr_interval_1d usage (cost=28.17..1,884.38 rows=4 width=8) (actual time=2.589..4.636 rows=2 loops=8,488)

  • Recheck Cond: (user_id = premises.user_id)
  • Filter: ((measured_at_range && '["2019-08-27 00:00:00","2019-09-02 23:59:59.999"]'::tsrange) AND (user_id <> 1009041))
  • Rows Removed by Filter: 396
  • Heap Blocks: exact=3038288
9. 12,884.784 12,884.784 ↑ 1.3 398 8,488

Bitmap Index Scan on utility_bozeman_usage_5yr_interval_1d_user_id_index (cost=0.00..28.17 rows=498 width=0) (actual time=1.518..1.518 rows=398 loops=8,488)

  • Index Cond: (user_id = premises.user_id)
10. 40,340.333 40,340.333 ↓ 1,000.0 1,000 1

CTE Scan on agg_usage_by_acct (cost=0.00..0.02 rows=1 width=4) (actual time=40,338.708..40,340.333 rows=1,000 loops=1)

Planning time : 32.889 ms
Execution time : 40,340.727 ms