Skip to content

Instantly share code, notes, and snippets.

@Komzpa

Komzpa/gist:8d5b9008ad60f9ccc62423c256e78b4c Secret

Created July 17, 2019 21:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Komzpa/8d5b9008ad60f9ccc62423c256e78b4c to your computer and use it in GitHub Desktop.
Save Komzpa/8d5b9008ad60f9ccc62423c256e78b4c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
-- no OFFSET 0, work in leader
00:11:38 [gis] > explain verbose create table osm_population_raw_nowater as (
select population,
osm_type,
osm_id,
admin_level,
null::float as area,
null::float as people_per_sq_km,
ST_Collect(geom) as geom
from (
select population,
osm_type,
osm_id,
admin_level,
coalesce(ST_Difference(p_geom, ST_Buffer(w_geom, 0)), p_geom) as geom
from tmp_osm_population_raw_nowater
) z
group by 1, 2, 3, 4
);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ GroupAggregate (cost=25498821.57..855235098.24 rows=1658933 width=80) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, NULL::double precision, NULL::double precision, st_collect(COALESCE(st_difference(tmp_osm_population_raw_nowater.p_geom, st_buffer(tmp_osm_population_raw_nowater.w_geom, '0'::double precision, ''::text)), tmp_osm_population_raw_nowater.p_geom)) │
│ Group Key: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level │
│ -> Sort (cost=25498821.57..25540294.90 rows=16589334 width=5068) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, tmp_osm_population_raw_nowater.p_geom, tmp_osm_population_raw_nowater.w_geom │
│ Sort Key: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level │
│ -> Gather (cost=100.00..2353996.50 rows=16589334 width=5068) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, tmp_osm_population_raw_nowater.p_geom, tmp_osm_population_raw_nowater.w_geom │
│ Workers Planned: 32 │
│ -> Parallel Seq Scan on public.tmp_osm_population_raw_nowater (cost=0.00..2337307.17 rows=518417 width=5068) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, tmp_osm_population_raw_nowater.p_geom, tmp_osm_population_raw_nowater.w_geom │
│ JIT: │
│ Functions: 7 │
│ Options: Inlining true, Optimization true, Expressions true, Deforming true │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(14 rows)
Time: 85,250 ms
-- with OFFSET 0, work is in parallel worker
00:11:45 [gis] > explain verbose create table osm_population_raw_nowater as (
select population,
osm_type,
osm_id,
admin_level,
null::float as area,
null::float as people_per_sq_km,
ST_Collect(geom) as geom
from (
select population,
osm_type,
osm_id,
admin_level,
coalesce(ST_Difference(p_geom, ST_Buffer(w_geom, 0)), p_geom) as geom
from tmp_osm_population_raw_nowater offset 0
) z
group by 1, 2, 3, 4
);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ GroupAggregate (cost=30795434.56..31065011.23 rows=1658933 width=80) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, NULL::double precision, NULL::double precision, st_collect((COALESCE(st_difference(tmp_osm_population_raw_nowater.p_geom, st_buffer(tmp_osm_population_raw_nowater.w_geom, '0'::double precision, ''::text)), tmp_osm_population_raw_nowater.p_geom))) │
│ Group Key: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level │
│ -> Sort (cost=30795434.56..30836907.89 rows=16589334 width=64) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, (COALESCE(st_difference(tmp_osm_population_raw_nowater.p_geom, st_buffer(tmp_osm_population_raw_nowater.w_geom, '0'::double precision, ''::text)), tmp_osm_population_raw_nowater.p_geom)) │
│ Sort Key: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level │
│ -> Gather (cost=100.00..28274846.50 rows=16589334 width=64) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, (COALESCE(st_difference(tmp_osm_population_raw_nowater.p_geom, st_buffer(tmp_osm_population_raw_nowater.w_geom, '0'::double precision, ''::text)), tmp_osm_population_raw_nowater.p_geom)) │
│ Workers Planned: 32 │
│ -> Parallel Seq Scan on public.tmp_osm_population_raw_nowater (cost=0.00..28258157.17 rows=518417 width=64) │
│ Output: tmp_osm_population_raw_nowater.population, tmp_osm_population_raw_nowater.osm_type, tmp_osm_population_raw_nowater.osm_id, tmp_osm_population_raw_nowater.admin_level, COALESCE(st_difference(tmp_osm_population_raw_nowater.p_geom, st_buffer(tmp_osm_population_raw_nowater.w_geom, '0'::double precision, ''::text)), tmp_osm_population_raw_nowater.p_geom) │
│ JIT: │
│ Functions: 7 │
│ Options: Inlining true, Optimization true, Expressions true, Deforming true │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(14 rows)
Time: 5,317 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment