explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gbaO

Settings
# exclusive inclusive rows x rows loops node
1. 3.705 7,939.245 ↓ 46.7 9,338 1

Sort (cost=84,241.75..84,242.25 rows=200 width=40) (actual time=7,938.433..7,939.245 rows=9,338 loops=1)

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

CTE data_timestamps

3. 4.149 4.149 ↓ 9.3 9,338 1

Function Scan on generate_series (cost=0.00..12.50 rows=1,000 width=8) (actual time=0.729..4.149 rows=9,338 loops=1)

4.          

CTE fuel_prices

5. 3.169 3.519 ↑ 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.491..3.519 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.350 0.350 ↑ 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.350..0.350 rows=2,704 loops=1)

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

HashAggregate (cost=82,822.09..82,824.59 rows=200 width=40) (actual time=7,931.292..7,935.540 rows=9,338 loops=1)

  • Group Key: data_timestamps.d
8. 5,763.050 7,918.715 ↑ 32.1 9,365 1

Nested Loop Left Join (cost=0.00..81,317.09 rows=301,000 width=8) (actual time=6.109..7,918.715 rows=9,365 loops=1)

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

CTE Scan on data_timestamps (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.731..7.925 rows=9,338 loops=1)

10. 2,147.740 2,147.740 ↑ 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.230 rows=2,697 loops=9,338)