[postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade
Regina Obe
lr at pcorp.us
Sun Feb 2 19:37:49 PST 2020
The ST_3DDWithin in PostGIS 3 was changed to use the new support function logic in 12. I assume that is where the &&& ST_Expand is coming from, though I'm surprised as I didn't think that would be seen in the planner.
Just to confirm we are talking about the same thing. Your definition for ST_3DDWithin for 12 should look like below? Can you confirm it does?
CREATE OR REPLACE FUNCTION st_3ddwithin(
geom1 geometry,
geom2 geometry,
double precision)
RETURNS boolean
LANGUAGE 'c'
COST 10000
IMMUTABLE STRICT PARALLEL SAFE
SUPPORT public.postgis_index_supportfn
AS '$libdir/postgis-3', 'LWGEOM_dwithin3d';
If it does, can you try changing the function to your old 11 definition and see if that makes the answers the same. Then at least we'll know it's the change in definition and can work from there.
Old 2.5 definition looks like this - make sure to replace the @extschema@ with with schema you have postgis installed in
CREATE OR REPLACE FUNCTION ST_3DDWithin(geom1 geometry, geom2 geometry,float8)
RETURNS boolean
AS 'SELECT $1 OPERATOR(@extschema at .&&) @extschema at .ST_Expand($2,$3) AND $2 OPERATOR(@extschema at .&&) @extschema at .ST_Expand($1,$3) AND @extschema at ._ST_3DDWithin($1, $2, $3)'
LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE
COST 100;
Hope that helps,
Regina
-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Tom Kazimiers
Sent: Saturday, February 1, 2020 10:52 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade
Hi,
I am in the process of evaluating an upgrade from Postgres 11 to 12 and PostGIS 2.5 to 3. For one central query in my application the same plan is executed in slightly different ways in the older PG11/PGIS2.5 setup compared to the new one. I wonder if anyone has some insight why this might be and if there is anything I could do about it?
The two servers are configured the same way and have both plenty of resources (32 GB shared memory, NVME storage, many cores, ).
The problematic query returns a set of PostGIS 3D edges (LineStringZ) through a bounding box query in a 3D space [1]. In the new setup, the query takes more than four times as long compared to the previous setup (repeatable). Surprisingly though, the plan is the same in both versions.
If I compare the index scan in the plans of the old version [2] with the one in the new version [3], I see that in Postgres 12, the index condition also includes
AND (edge &&& st_expand(' '))
and the index condition in Postgres 11 doesn't. This terms seems to be part of the filter field in Postgres 11 though. Could this contribute to the timings difference I see?
In case it is relevant I also attached the definition of tables plus their indices in [4]. Both tables have the same size (~50,000,000), the table treenode_edge in fact just points to the treenode table and has one entry per treenode row (using a FK, didn't copy constraints below).
Thanks,
Tom
[1] Typical SQL query:
WITH bb_edge AS (
SELECT te.id, te.parent_id
FROM treenode_edge te
WHERE te.edge &&& ST_MakeLine(ARRAY[
ST_MakePoint(471548.0, 330140.0, 160440.0),
ST_MakePoint(542460.0, 290140.0, 160400.0)] ::geometry[])
AND ST_3DDWithin(te.edge, ST_MakePolygon(ST_MakeLine(ARRAY[
ST_MakePoint(471548.0, 290140.0, 160420.0),
ST_MakePoint(542460.0, 290140.0, 160420.0),
ST_MakePoint(542460.0, 330140.0, 160420.0),
ST_MakePoint(471548.0, 330140.0, 160420.0),
ST_MakePoint(471548.0, 290140.0, 160420.0)]::geometry[])),
20.0)
AND te.project_id = 1
)
SELECT
t1.id,
t1.parent_id,
t1.location_x,
t1.location_y,
t1.location_z,
t1.confidence,
t1.radius,
t1.skeleton_id,
EXTRACT(EPOCH FROM t1.edition_time),
t1.user_id
FROM (
SELECT id FROM bb_edge
UNION
SELECT parent_id FROM bb_edge
UNION
SELECT UNNEST(ARRAY[41851780,25932042,25932043,14274317,25932045,
25932050,25932052,41876633,25932070,42769832,25932073,25932076,
42769838,25932082,25932084,27641652,25220534,25932087,41421110,
26002235,36359611,25932094,36359614,41421118,41420481,41876677,
25932101,27641926,27641927,27642056,25932233,41420487,25932235,
25932108,27642059,40327244,25932111,25932243,25967062,25967066,
25967067,25967069,25967070,25932130,27642082,25932132,27642085,
25967080,25967082,25967084,27642099,28873207]::bigint[])
) edges(edge_child_id)
JOIN treenode t1
ON edge_child_id = t1.id
LIMIT 80000
[2] Postgres 11 plan:
Limit (cost=347.61..714.75 rows=102 width=50) (actual time=28.258..39.087 rows=1569 loops=1)
Buffers: shared hit=9468
CTE bb_edge
-> Index Scan using treenode_edge_gix on treenode_edge te (cost=0.42..344.70 rows=1 width=16) (actual time=0.569..26.662 rows=1004 loops=1)
Index Cond: (edge &&& '01020000800200000000000000F0C71C41000000007026144100000000C095034100000000F88D20410000000070B511410000000080940341'::geometry)
Filter: ((edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry) AND (project_id = 1) AND ('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry && st_expand(edge, '20'::double precision)) AND _st_3ddwithin(edge, '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
Rows Removed by Filter: 5
Buffers: shared hit=1616
-> Nested Loop (cost=2.91..370.05 rows=102 width=50) (actual time=28.256..38.880 rows=1569 loops=1)
Buffers: shared hit=9468
-> HashAggregate (cost=2.34..3.36 rows=102 width=8) (actual time=28.220..28.600 rows=1570 loops=1)
Group Key: bb_edge.id
Buffers: shared hit=1616
-> Append (cost=0.00..2.09 rows=102 width=8) (actual time=0.576..27.493 rows=2060 loops=1)
Buffers: shared hit=1616
-> CTE Scan on bb_edge (cost=0.00..0.02 rows=1 width=8) (actual time=0.575..27.167 rows=1004 loops=1)
Buffers: shared hit=1616
-> CTE Scan on bb_edge bb_edge_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.126 rows=1004 loops=1)
-> ProjectSet (cost=0.00..0.52 rows=100 width=8) (actual time=0.007..0.012 rows=52 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
-> Index Scan using treenode_pkey on treenode t1 (cost=0.56..3.58 rows=1 width=50) (actual time=0.006..0.006 rows=1 loops=1570)
Index Cond: (id = bb_edge.id)
Buffers: shared hit=7852
Planning Time: 1.159 ms
Execution Time: 39.268 ms
[3] Postgres 12 plan:
Limit (cost=32.79..230.14 rows=54 width=54) (actual time=194.177..205.356 rows=1569 loops=1)
Buffers: shared hit=14391
CTE bb_edge
-> Index Scan using treenode_edge_gix on treenode_edge te (cost=0.67..28.71 rows=1 width=16) (actual time=0.814..192.481 rows=1004 loops=1)
Index Cond: ((edge &&& '01020000800200000000000000F0C71C41000000007026144100000000C095034100000000F88D20410000000070B511410000000080940341'::geometry) AND (edge &&& st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision)))
Filter: ((project_id = 1) AND st_3ddwithin(edge, '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
Rows Removed by Filter: 5
Buffers: shared hit=6546
-> Nested Loop (cost=4.08..201.42 rows=54 width=54) (actual time=194.176..205.161 rows=1569 loops=1)
Buffers: shared hit=14391
-> HashAggregate (cost=3.51..5.13 rows=54 width=8) (actual time=194.139..194.451 rows=1570 loops=1)
Group Key: bb_edge.id
Buffers: shared hit=6546
-> Append (cost=0.00..3.38 rows=54 width=8) (actual time=0.821..193.400 rows=2060 loops=1)
Buffers: shared hit=6546
-> CTE Scan on bb_edge (cost=0.00..0.06 rows=1 width=8) (actual time=0.820..193.052 rows=1004 loops=1)
Buffers: shared hit=6546
-> CTE Scan on bb_edge bb_edge_1 (cost=0.00..0.06 rows=1 width=8) (actual time=0.002..0.124 rows=1004 loops=1)
-> ProjectSet (cost=0.00..0.83 rows=52 width=8) (actual time=0.006..0.012 rows=52 loops=1)
-> Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
-> Index Scan using treenode_id_pkey on treenode t1 (cost=0.56..3.60 rows=1 width=54) (actual time=0.006..0.006 rows=1 loops=1570)
Index Cond: (id = bb_edge.id)
Buffers: shared hit=7845
Planning Time: 1.115 ms
Execution Time: 205.704 ms
[4] Table definitions:
# \d treenode_edge
Table "public.treenode_edge"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
id | bigint | | not null |
parent_id | bigint | | |
project_id | integer | | not null |
edge | geometry(LineStringZ) | | not null |
Indexes:
"treenode_edge_pkey" PRIMARY KEY, btree (id)
"treenode_edge_2d_gist" gist (edge)
"treenode_edge_gix" gist (edge gist_geometry_ops_nd)
"treenode_edge_project_id_index" btree (project_id)
"treenode_edge_z_range_gist" gist (floatrange(st_zmin(edge::box3d),
st_zmax(edge::box3d), '[]'::text))
# \d treenode
Table "public.treenode"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+------
---------------+--------------------------+-----------+----------+--
id | bigint | | not null | nextval('location_id_seq'::regclass)
project_id | integer | | not null |
location_x | real | | not null |
location_y | real | | not null |
location_z | real | | not null |
editor_id | integer | | not null |
user_id | integer | | not null |
creation_time | timestamp with time zone | | not null | now()
edition_time | timestamp with time zone | | not null | now()
txid | bigint | | | txid_current()
skeleton_id | bigint | | not null |
parent_id | bigint | | |
radius | real | | not null | 0
confidence | smallint | | not null | 5
Indexes:
"treenode_id_pkey" PRIMARY KEY, btree (id)
"treenode_creation_time_idx" btree (creation_time)
"treenode_edition_time_idx" btree (edition_time)
"treenode_parent_id_idx" btree (parent_id)
"treenode_project_id_location_x_idx" btree (project_id, location_x)
"treenode_project_id_location_y_idx" btree (project_id, location_y)
"treenode_project_id_location_z_idx" btree (project_id, location_z)
"treenode_project_id_user_id_idx" btree (user_id, project_id)
"treenode_skeleton_id_project_id_idx" btree (skeleton_id, project_id)
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list