explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfPV : ordered brin

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.113 2,699.957 ↑ 1.0 1 1

Aggregate (cost=408.10..408.11 rows=1 width=12) (actual time=2,699.957..2,699.957 rows=1 loops=1)

2.          

CTE agg_usage_by_acct

3. 0.078 2,699.682 ↓ 1,000.0 1,000 1

Limit (cost=408.05..408.08 rows=1 width=8) (actual time=2,698.249..2,699.682 rows=1,000 loops=1)

4. 0.920 2,699.604 ↓ 1,000.0 1,000 1

GroupAggregate (cost=408.05..408.08 rows=1 width=8) (actual time=2,698.247..2,699.604 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. 5.125 2,698.684 ↓ 5,250.0 5,250 1

Sort (cost=408.05..408.06 rows=1 width=8) (actual time=2,698.228..2,698.684 rows=5,250 loops=1)

  • Sort Key: usage.user_id
  • Sort Method: quicksort Memory: 1727kB
6. 12.916 2,693.559 ↓ 20,440.0 20,440 1

Nested Loop (cost=383.65..408.04 rows=1 width=8) (actual time=8.088..2,693.559 rows=20,440 loops=1)

7. 923.627 923.627 ↓ 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.286..923.627 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. 25.464 1,757.016 ↑ 2.0 2 8,488

Bitmap Heap Scan on pw_usage_5y_interval_1d usage (cost=383.37..399.32 rows=4 width=8) (actual time=0.206..0.207 rows=2 loops=8,488)

  • Recheck Cond: ((user_id = premises.user_id) AND (measured_at_range && '["2019-08-27 00:00:00","2019-09-02 23:59:59.999"]'::tsrange))
  • Rows Removed by Index Recheck: 0
  • Filter: (user_id <> 1009041)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=21188
9. 229.826 1,731.552 ↓ 0.0 0 8,488

BitmapAnd (cost=383.37..383.37 rows=4 width=0) (actual time=0.204..0.204 rows=0 loops=8,488)

10. 398.936 398.936 ↑ 1.2 398 8,488

Bitmap Index Scan on pw_usage_5yr_interval_1d_user_id_measured_at_index (cost=0.00..20.27 rows=495 width=0) (actual time=0.047..0.047 rows=398 loops=8,488)

  • Index Cond: (user_id = premises.user_id)
11. 1,102.790 1,102.790 ↑ 17.5 2,560 8,483

Bitmap Index Scan on pw_usage_5yr_interval_1d_measured_at_index (cost=0.00..351.66 rows=44,754 width=0) (actual time=0.130..0.130 rows=2,560 loops=8,483)

  • Index Cond: (measured_at_range && '["2019-08-27 00:00:00","2019-09-02 23:59:59.999"]'::tsrange)
12. 2,699.844 2,699.844 ↓ 1,000.0 1,000 1

CTE Scan on agg_usage_by_acct (cost=0.00..0.02 rows=1 width=4) (actual time=2,698.251..2,699.844 rows=1,000 loops=1)

Planning time : 17.746 ms
Execution time : 2,700.110 ms