explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ip5

Settings
# exclusive inclusive rows x rows loops node
1. 4.039 10,416.268 ↓ 46.7 9,338 1

Sort (cost=70,706.75..70,707.25 rows=200 width=48) (actual time=10,415.430..10,416.268 rows=9,338 loops=1)

  • Sort Key: data_timestamps.d
  • Sort Method: quicksort Memory: 1114kB
2.          

CTE data_timestamps

3. 7.494 7.494 ↓ 9.3 9,338 1

Function Scan on generate_series (cost=0.00..22.50 rows=1,000 width=16) (actual time=0.626..7.494 rows=9,338 loops=1)

4.          

CTE fuel_prices

5. 2.625 2.950 ↑ 1.0 2,697 1

Bitmap Heap Scan on fuel_prices fuel_prices_1 (cost=101.54..1,397.02 rows=2,709 width=77) (actual time=0.457..2.950 rows=2,697 loops=1)

  • Recheck Cond: (petrol_station_id = '97fc6bee-3424-4c39-9842-424629fa89b4'::text)
  • Filter: ((insert_time >= '2018-08-30 10:45:00+02'::timestamp with time zone) AND (insert_time <= '2018-12-05 16:00:00+01'::timestamp with time zone))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=954
6. 0.325 0.325 ↑ 1.0 2,704 1

Bitmap Index Scan on fuel_prices_petrol_station_id_idx (cost=0.00..100.86 rows=2,726 width=0) (actual time=0.325..0.325 rows=2,704 loops=1)

  • Index Cond: (petrol_station_id = '97fc6bee-3424-4c39-9842-424629fa89b4'::text)
7. 17.484 10,412.229 ↓ 46.7 9,338 1

HashAggregate (cost=69,277.09..69,279.59 rows=200 width=48) (actual time=10,407.919..10,412.229 rows=9,338 loops=1)

  • Group Key: data_timestamps.d
8. 8,160.725 10,394.745 ↑ 32.1 9,365 1

Nested Loop Left Join (cost=0.00..67,772.09 rows=301,000 width=12) (actual time=5.625..10,394.745 rows=9,365 loops=1)

  • Join Filter: ((fuel_prices.insert_time >= data_timestamps.d) AND (fuel_prices.insert_time <= data_timestamps.d1))
  • Rows Removed by Join Filter: 25181889
9. 11.576 11.576 ↓ 9.3 9,338 1

CTE Scan on data_timestamps (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.628..11.576 rows=9,338 loops=1)

10. 2,222.444 2,222.444 ↑ 1.0 2,697 9,338

CTE Scan on fuel_prices (cost=0.00..54.18 rows=2,709 width=12) (actual time=0.000..0.238 rows=2,697 loops=9,338)