[postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade
Tom Kazimiers
tom at voodoo-arts.net
Sat Feb 1 19:51:40 PST 2020
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)
More information about the postgis-users
mailing list