[postgis-tickets] [PostGIS] #4372: PROJ 6: 4x slowdown on indexes over ST_Transform
PostGIS
trac at osgeo.org
Wed Apr 10 08:32:47 PDT 2019
#4372: PROJ 6: 4x slowdown on indexes over ST_Transform
------------------------+---------------------------
Reporter: Algunenano | Owner: Algunenano
Type: defect | Status: assigned
Priority: high | Milestone: PostGIS 3.0.0
Component: postgis | Version: trunk
Keywords: |
------------------------+---------------------------
# Setup
{{{
# Select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by clang version 8.0.0
(tags/RELEASE_800/final), 64-bit
}}}
A table with an index over st_transform.
The table was created as:
{{{
CREATE TABLE analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
AS
SELECT * FROM (SELECT
ST_Buffer(the_geom::geography, 50000)::geometry
the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
FROM (select * from populated_places_simple_reduced) _camshaft_buffer)
_analysis_create_table_query
LIMIT 0
}}}
And the index as:
{{{
CREATE INDEX ON
analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 USING GIST
(ST_Transform(the_geom, 3857))
}}}
The table looks like this:
{{{
# \d+ analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
Table
"public.analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
the_geom | geometry | | | |
main | |
scalerank | integer | | | |
plain | |
natscale | integer | | | |
plain | |
labelrank | integer | | | |
plain | |
featurecla | text | | | |
extended | |
name | text | | | |
extended | |
namepar | text | | | |
extended | |
namealt | text | | | |
extended | |
diffascii | integer | | | |
plain | |
nameascii | text | | | |
extended | |
adm0cap | double precision | | | |
plain | |
capalt | double precision | | | |
plain | |
capin | text | | | |
extended | |
worldcity | double precision | | | |
plain | |
megacity | integer | | | |
plain | |
sov0name | text | | | |
extended | |
sov_a3 | text | | | |
extended | |
adm0name | text | | | |
extended | |
adm0_a3 | text | | | |
extended | |
adm1name | text | | | |
extended | |
iso_a2 | text | | | |
extended | |
note | text | | | |
extended | |
latitude | double precision | | | |
plain | |
longitude | double precision | | | |
plain | |
changed | double precision | | | |
plain | |
namediff | integer | | | |
plain | |
diffnote | text | | | |
extended | |
pop_max | integer | | | |
plain | |
pop_min | integer | | | |
plain | |
pop_other | integer | | | |
plain | |
geonameid | double precision | | | |
plain | |
meganame | text | | | |
extended | |
ls_name | text | | | |
extended | |
ls_match | integer | | | |
plain | |
checkme | integer | | | |
plain | |
cartodb_id | integer | | | |
plain | |
created_at | timestamp with time zone | | | |
plain | |
updated_at | timestamp with time zone | | | |
plain | |
Indexes:
"analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9_st_transform_idx" gist
(st_transform(the_geom, 3857))
}}}
# Testing with PROJ 5.2 and postgis trunk:
{{{
# INSERT INTO analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4
SELECT
ST_Buffer(the_geom::geography, 50000)::geometry
the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
FROM (select * from populated_places_simple_reduced) _camshaft_buffer;
INSERT 0 7313
Time: 1860.093 ms (00:01.860)
}}}
I've run the same test several times and it always takes ~1.8s.
perf divided by .so:
{{{
Perf by .so:
+ 82.25% 0.00% [unknown]
+ 81.89% 18.38% libc-2.28.so
+ 67.54% 41.42% postgres
+ 58.06% 6.69% postgis-3.so
+ 15.06% 13.08% libproj.so.13.1.1
+ 11.65% 0.56% libgeos_c.so.1.11.0
+ 11.38% 8.73% libgeos-3.8.0.so
+ 6.17% 6.06% libm-2.28.so
+ 3.25% 2.99% [kernel.kallsyms]
+ 1.90% 0.05% libpthread-2.28.so
+ 1.84% 1.71% libstdc++.so.6.0.25
0.34% 0.34% ld-2.28.so
0.00% 0.00% [vdso]
}}}
# Same with PROJ 6 (just built from master, latest ref is
fb125618fd18f112ed6f37662b021d07a602ff90), and also postgis trunk (rebuilt
with the dependency):
{{{
test_windshaft_cartodb_user_1_db=# INSERT INTO
analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4 SELECT
ST_Buffer(the_geom::geography, 50000)::geometry
the_geom,scalerank,natscale,labelrank,featurecla,name,namepar,namealt,diffascii,nameascii,adm0cap,capalt,capin,worldcity,megacity,sov0name,sov_a3,adm0name,adm0_a3,adm1name,iso_a2,note,latitude,longitude,changed,namediff,diffnote,pop_max,pop_min,pop_other,geonameid,meganame,ls_name,ls_match,checkme,cartodb_id,created_at,updated_at
FROM (select * from populated_places_simple_reduced) _camshaft_buffer;
INSERT 0 7313
Time: 7184.529 ms (00:07.185)
}}}
Running it several times shows it always takes ~7.2 secs, that's as slow
as the build using PROJ 5.2.
Perf by .so:
{{{
Perf by .so:
+ 87.70% 33.62% libc-2.28.so
+ 79.96% 0.00% [unknown]
+ 61.70% 14.32% postgres
+ 59.86% 1.77% postgis-3.so
+ 45.09% 25.42% libproj.so.15.0.0
+ 10.26% 8.80% libstdc++.so.6.0.25
+ 7.02% 6.98% [kernel.kallsyms]
+ 3.72% 0.48% libicuuc.so.64.1
+ 3.48% 3.42% libgcc_s.so.1
+ 2.94% 0.19% libgeos_c.so.1.11.0
+ 2.83% 2.29% libgeos-3.8.0.so
+ 1.95% 1.90% libm-2.28.so
1.02% 0.38% libpthread-2.28.so
0.37% 0.37% libsqlite3.so.0.8.6
0.06% 0.05% ld-2.28.so
0.00% 0.00% libk5crypto.so.3.1
0.00% 0.00% libxml2.so.2.9.9
0.00% 0.00% [vdso]
}}}
If I run only ST_Transform over the table, the times are more similar;
proj6 is still slower but not 4x slower:
Running only ST_Transform (`Select ST_Transform(the_geom, 3857) from
analysis_2f13a3dbd7_9de00c87a82f0929cfc345d2d9f1d01e05811bc4;`)
PROJ 5 build:
Time: 1558.226 ms (00:01.558)
Time: 1570.106 ms (00:01.570)
Time: 1565.649 ms (00:01.566)
PROJ 6 build:
Time: 1764.034 ms (00:01.764)
Time: 1710.738 ms (00:01.711)
Time: 1732.082 ms (00:01.732)
Have we lost some caching in the proj6 update?
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4372>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list