explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0smA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=35,474,135.05..35,476,039.29 rows=95,212 width=16) (actual rows= loops=)

  • Group Key: zz.shape_id, zz.ntile_bucket
2.          

CTE shape_id_overlap

3. 0.000 0.000 ↓ 0.0

Unique (cost=436,856.88..436,861.81 rows=985 width=4) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=436,856.88..436,859.34 rows=985 width=4) (actual rows= loops=)

  • Sort Key: a_1.shape_id
5. 0.000 0.000 ↓ 0.0

Gather (cost=10,200.25..436,807.91 rows=985 width=4) (actual rows= loops=)

  • Workers Planned: 2
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9,200.25..435,709.41 rows=410 width=4) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on shapefile b_1 (cost=9,199.84..21,573.92 rows=248 width=9,323) (actual rows= loops=)

  • Recheck Cond: ((enddate IS NULL) AND (startdate = '2013-01-01 00:00:00'::timestamp without time zone))
  • Filter: ((summarylevelid)::text = '140'::text)
8. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9,199.84..9,199.84 rows=3,194 width=0) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_exposome_pici_shapefile_enddate (cost=0.00..511.56 rows=27,618 width=0) (actual rows= loops=)

  • Index Cond: (enddate IS NULL)
10. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_exposome_pici_shapefile_startdate (cost=0.00..8,687.73 rows=469,773 width=0) (actual rows= loops=)

  • Index Cond: (startdate = '2013-01-01 00:00:00'::timestamp without time zone)
11. 0.000 0.000 ↓ 0.0

Index Scan using idx_shapefile_geometrywkt on shapefile a_1 (cost=0.41..1,669.89 rows=1 width=9,327) (actual rows= loops=)

  • Index Cond: (geometrywkt && b_1.geometrywkt)
  • Filter: (((summarylevelid)::text = '140'::text) AND (startdate = '2010-01-01 00:00:00'::timestamp without time zone) AND (enddate = '2010-12-31 00:00:00'::timestamp without time zone) AND ((st_area(st_intersection(st_makevalid(geometrywkt), st_makevalid(b_1.geometrywkt))) / st_area(b_1.geometrywkt)) >= '0.01'::double precision) AND _st_intersects(geometrywkt, b_1.geometrywkt))
12. 0.000 0.000 ↓ 0.0

Sort (cost=35,037,273.24..35,037,511.27 rows=95,212 width=16) (actual rows= loops=)

  • Sort Key: zz.shape_id, zz.ntile_bucket
13. 0.000 0.000 ↓ 0.0

Subquery Scan on zz (cost=35,025,829.31..35,029,399.76 rows=95,212 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

WindowAgg (cost=35,025,829.31..35,028,447.64 rows=95,212 width=16) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Sort (cost=35,025,829.31..35,026,067.34 rows=95,212 width=12) (actual rows= loops=)

  • Sort Key: a.shape_id, (((a.data ->> 'air_temperature_f_mean_prediction_autokrig'::text))::double precision)
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=32.72..35,017,955.82 rows=95,212 width=12) (actual rows= loops=)

  • Hash Cond: (a.shape_id = b.shape_id_acs)
17. 0.000 0.000 ↓ 0.0

Index Scan using idx_exposome_pici_facttable_noaa_data_id_startdate_enddate on facttable_noaa a (cost=0.71..33,763,197.35 rows=10,024,482 width=574) (actual rows= loops=)

  • Index Cond: ((data_id = 118295) AND (startdate >= '2013-01-01 00:00:01'::timestamp without time zone) AND (startdate <= '2015-01-01 00:00:01'::timestamp without time zone) AND (enddate >= '2013-01-01 23:59:59'::timestamp without time zone) AND (enddate <= '2015-01-01 23:59:59'::timestamp without time zone))
18. 0.000 0.000 ↓ 0.0

Hash (cost=19.70..19.70 rows=985 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

CTE Scan on shape_id_overlap b (cost=0.00..19.70 rows=985 width=4) (actual rows= loops=)