Skip to content

Instantly share code, notes, and snippets.

@nyurik
Last active May 19, 2020 22:23
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 nyurik/2aaad488a44ad4d93f45400cfafb20d5 to your computer and use it in GitHub Desktop.
Save nyurik/2aaad488a44ad4d93f45400cfafb20d5 to your computer and use it in GitHub Desktop.
Optimizing LabelGrid - the result is worse than before??
------- Testing:
-- git clone https://github.com/openmaptiles/openmaptiles
-- git checkout upgrade-v5-pg12
-- place this file in the openmaptiles/ dir as "test-func.sql"
-- use make start-db to create a new database (in docker)
-- use make bash to start tools (another docker)
-- test with this command. The test call is taken from the openmaptiles-tools/tests/sql/LabelGrid.sql test. Note the "volatile" keyword - without it the query planner will optimize away multiple calls with the same value.
-- profile-pg-func --file test-func.sql "LabelGrid_pgsql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343)" "LabelGrid_sql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343)"
-- The results are not that great:
-- Function AVG of 8 runs MIN MAX STDEV
-- ---------------------------------------------------------------------- --------------- -------------- -------------- --------
-- LabelGrid_pgsql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343) 0:00:01.060830 0:00:00.936732 0:00:01.235901 0.111131
-- LabelGrid_sql(ST_GeomFromText('POINT(100 -100)',900913), 64*9.5546285343) 0:00:02.010954 0:00:01.748449 0:00:02.192110 0.152105
create or replace function LabelGrid_pgsql (
g geometry,
grid_size numeric
)
returns text
language plpgsql volatile
parallel safe as
$func$
begin
if grid_size <= 0 then
return 'null';
end if;
if GeometryType(g) <> 'POINT' then
g := (select (ST_DumpPoints(g)).geom limit 1);
end if;
return ST_AsText(ST_SnapToGrid(
g,
grid_size/2, -- x origin
grid_size/2, -- y origin
grid_size, -- x size
grid_size -- y size
));
end;
$func$;
create or replace function LabelGrid_sql (
g geometry,
grid_size numeric
)
returns text
language sql volatile
parallel safe as
$func$
SELECT CASE
WHEN grid_size <= 0 then 'null'
ELSE ST_AsText(ST_SnapToGrid(
CASE
WHEN GeometryType(g) <> 'POINT' then (select (ST_DumpPoints(g)).geom limit 1)
ELSE g
END,
grid_size/2, -- x origin
grid_size/2, -- y origin
grid_size, -- x size
grid_size -- y size
))
end;
$func$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment