Last active
May 19, 2020 22:23
-
-
Save nyurik/2aaad488a44ad4d93f45400cfafb20d5 to your computer and use it in GitHub Desktop.
Optimizing LabelGrid - the result is worse than before??
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------- 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