[postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade
Tom Kazimiers
tom at voodoo-arts.net
Wed Mar 4 21:39:06 PST 2020
Hi Paul,
Thank you for your continued effort to find out what's going on here!
This is all indeed very strange.
I just set up a second test database with more entries (50 million)
using 12.2/2.5.3 and ran the relevant query from the initial post
(returning ~1000 rows) in this database as well as 12.2/3.0 and
11/2.5.3:
EXPLAIN (ANALYZE, BUFFERS)
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;
11.7/2.5.2:
Index Scan using treenode_edge_gix on treenode_edge te (cost=0.42..353.59 rows=1 width=16) (actual time=5.386..172.324 rows=1005 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=302 read=1349 dirtied=2
Planning Time: 1.324 ms
Execution Time: 172.535 ms
12/2.5.3:
Index Scan using treenode_edge_gix on treenode_edge te (cost=0.42..391.68 rows=1 width=16) (actual time=0.936..27.404 rows=1005 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=1370
Planning Time: 0.782 ms
Execution Time: 27.518 ms
12/3.0:
Index Scan using treenode_edge_gix on treenode_edge te (cost=0.67..28.71 rows=1 width=16) (actual time=0.730..163.358 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
Planning Time: 1.312 ms
Execution Time: 163.471 ms
This is all very strange. Like in the initial mail, I don't see any
bitmap scans anymore, only the index scan with different runtimes. Also,
now the 12/2.5.3 query has now the speed of the original 11/2.5.3 query
(?!) from my first post. The estimates are wrong in on all version
though. All databases have had run ANALYZE right beforehand and have a
comparible configuration with the relevant part of the data in memory.
Also, changing the function cost of ST_3DDWithin, doesn't have any
effect anymore. I can't get it to run with the bitmap heap scan again
(which doesn't seem to play a role with bigger result sets anyway).
I suppose the main difference is that 12/2.5.3 was just recreatead from
a backup and is therefore tightly packed on disk (and memory?). Could
this have such a big influence?
The timings above suggest to me that neither Postgres 12 nor PostGIS 3
have a negative influence on performance here. I will run a vaccum and
reindex on the 12/3.0 setup (which might take a day or so) and see if
this changes anything.
In a previous mail you suggested to only look at the ST_3DDWithin part,
i,e, using this query:
EXPLAIN (ANALYZE, BUFFERS)
SELECT te.id, te.parent_id
FROM treenode_edge te
WHERE 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;
And this is how that looks like:
11/2.5.3:
Gather (cost=16804.64..590073.63 rows=30 width=16) (actual time=366.372..1123.021 rows=782 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=371525
-> Parallel Bitmap Heap Scan on treenode_edge te (cost=15804.64..589070.63 rows=8 width=16) (actual time=372.224..994.656 rows=156 loops=5)
Recheck Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
Filter: ((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: 187033
Heap Blocks: exact=97218
Buffers: shared hit=371525
-> Bitmap Index Scan on treenode_edge_2d_gist (cost=0.00..15804.63 rows=916441 width=0) (actual time=204.907..204.907 rows=1105737 loops=1)
Index Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
Buffers: shared hit=9758
Planning Time: 0.470 ms
JIT:
Functions: 30
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 4.219 ms, Inlining 197.812 ms, Optimization 188.993 ms, Emission 79.596 ms, Total 470.619 ms
Execution Time: 1124.520 ms
12/2.5.3:
Gather (cost=24934.73..862262.11 rows=79121 width=16) (actual time=192.531..770.051 rows=1005 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=104591
-> Parallel Bitmap Heap Scan on treenode_edge te (cost=23934.73..853350.01 rows=19780 width=16) (actual time=153.920..675.015 rows=201 loops=5)
Recheck Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
Filter: ((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: 244695
Heap Blocks: exact=23644
Buffers: shared hit=104591
-> Bitmap Index Scan on treenode_edge_2d_gist (cost=0.00..23914.95 rows=1220804 width=0) (actual time=158.067..158.068 rows=1224482 loops=1)
Index Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
Buffers: shared hit=10480
Planning Time: 0.470 ms
Execution Time: 770.358 ms
12/3.0:
Index Scan using treenode_edge_gix on treenode_edge te (cost=0.67..5577.07 rows=4755 width=16) (actual time=0.453..90.967 rows=1004 loops=1)
Index Cond: (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: 7
Buffers: shared hit=6547 read=4
Planning Time: 0.643 ms
Execution Time: 91.079 ms
Interestingly, here, 12/3.0 does better and doesn't attempt to
parallelize like you observed as well or try to use JIT (it's enabled in
all clusters). At least with PostGIS 2.5 it seems the extra &&& is also
helpful for performance (and I might need the &&& to limit the results a
bit more). Still, while the 12/3 ST_3DDWithin part is faster than the
whole thing on 12/3 run is faster now than the above 12/2.5.3 run.
Without knowing much about the internals of the indices used, I would
assume that a REINDEX could indeed help, because the index scan would
then likely need to cover less memory. If this doesn't explain the
timing difference, I will recreate the 12/3.0 database from scratch just
like I created the 12/2.5 database today (before I believe the 12/3.0
was created by `pg_upgrade --link`).
Best,
Tom
On Mon, Mar 02, 2020 at 01:40:50PM -0800, Paul Ramsey wrote:
>So, this gets stranger the more I poke, but not in any helpful way.
>I installed 3.0.2dev and 2.5.4dev in my Pg12 instance and ran the same
>query using your test file.
>
>https://github.com/tomka/tmp/blob/master/postgres/catmaid-postgis-test.pgsql
>pg_restore -O -f- catmaid-postgis-test.pgsql | psql postgis254d
>
>And using this test query:
>
>explain analyze SELECT Count(*) FROM (
>
> SELECT te.id, te.parent_id
>
> FROM
>treenode_edge te
>
> WHERE 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)
>) a;
>
>And I get a 8ms execution from 12/3 and a 128ms execution from 12/2.5!
>
>--- pg12/postgis3.0
>
> Aggregate (cost=584.97..584.98 rows=1 width=8) (actual
>time=7.155..7.155 rows=1 loops=1)
> -> Index Scan using treenode_edge_gix on treenode_edge te
>(cost=0.53..584.88 rows=33 width=0) (actual time=0.706..7.057 rows=143
>loops=1)
> Index Cond: (edge &&&
>st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry,
>'20'::double precision))
> Filter: st_3ddwithin(edge,
>'0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry,
>'20'::double precision)
> Planning Time: 2.843 ms
> Execution Time: 7.278 ms
>
>-- pg12/postgis2.5
>
> Aggregate (cost=14477.72..14477.73 rows=1 width=8) (actual
>time=148.645..148.645 rows=1 loops=1)
> -> Bitmap Heap Scan on treenode_edge te (cost=1142.85..14472.35
>rows=2151 width=0) (actual time=16.940..148.583 rows=143 loops=1)
> Recheck Cond: (edge &&
>'0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
> Filter:
>(('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry
>&& st_expand(edge, '20'::double precision)) AND _st_3ddwithin(edge,
>'0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry,
>'20'::double precision))
> Rows Removed by Filter: 32400
> Heap Blocks: exact=3507
> -> Bitmap Index Scan on treenode_edge_2d_gist
>(cost=0.00..1142.32 rows=32271 width=0) (actual time=15.024..15.024
>rows=32543 loops=1)
> Index Cond: (edge &&
>'0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
> Planning Time: 0.833 ms
> Execution Time: 148.855 ms
>
>Also, the 12/2.5 query wants to parallelize (???) while the 12/3 one does not.
>
>Anyways, it's super funky and not so helpful.
>
>The trouble with your test set is that it's too small (relative to the
>query?) so it's not processing many rows.
>
>If you could run a sampling profiler against your two queries that
>would pick up the places where the execution paths differ for the
>different versions, it's all I'm trying to do, but I haven't been able
>to even replicate the behavious so far, so I am at an impasse at this
>moment.
>
>P
>
>
>On Tue, Feb 4, 2020 at 1:01 PM Tom Kazimiers <tom at voodoo-arts.net> wrote:
>>
>> >> Thanks for the report. I've ticketed as a bug -
>> >> https://trac.osgeo.org/postgis/ticket/4635
>>
>> Thanks for filing the ticket Regina!
>>
>> On Tue, Feb 04, 2020 at 11:49:44AM -0800, Paul Ramsey wrote:
>> >OK, cleaning this out a little.
>> >Ticket for reference https://trac.osgeo.org/postgis/ticket/4635#comment:2
>>
>> "Good" to know that you seem to be able to reproduce this slow-down.
>>
>> >First, drop all the rest of your testing SQL and just test the contents
>> >of the bb_edge CTE. That seems to be sufficient, and it’s the only
>> >spatial part.
>>
>> Good point, I should have restricted it to that from beginning.
>>
>> >Second, for testing 3.0, drop the use of the &&& operator and see what happens with just the ST_DWithin3D function call, which should be throwing an index op in there implicitly.
>> >
>> >SELECT Count(*) FROM (
>> > SELECT te.id, te.parent_id
>> > FROM treenode_edge te
>> > WHERE 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)
>> >) a;
>> >
>> >We are definitely seeing different plans in that CTE.
>>
>> This is what I get in the test database with 12/3:
>>
>> Aggregate (cost=506.38..506.41 rows=1 width=8) (actual time=1.530..1.530 rows=1 loops=1)
>> Buffers: shared hit=198
>> -> Index Scan using treenode_edge_gix on treenode_edge te (cost=0.53..506.29 rows=33 width=0) (actual time=0.168..1.512 rows=143 loops=1)
>> Index Cond: (edge &&& st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
>> Filter: ((project_id = 1) AND st_3ddwithin(edge, '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
>> Buffers: shared hit=198
>> Planning Time: 0.654 ms
>> Execution Time: 1.583 ms
>>
>> Dropping the &&& operator still allows the use of the index it seems and
>> is noticeably faster. (In my original query, I used both &&& and
>> ST_3DDWithin, because I wanted to reduce the number of false positives
>> that I would get with only one of them in some densely populated areas.
>> The &&& would make sure every result object bounding box intersects with
>> the query bounding box and the ST_3DDWithin would ensure I don't get
>> edges that are actually only passing by somewhere close, but not
>> actually intersecting the field of view. The line/geometry used with
>> ST_3DDWithin is in fact a flat cut through the center of the query
>> bounding box. Therefore I believe I still need the &&& in production,
>> unfortunately.)
>>
>> This is what I get for the above query with the test database in the
>> 11/2.5 setup:
>>
>> Finalize Aggregate (cost=10302.14..10302.17 rows=1 width=8) (actual time=43.245..43.245 rows=1 loops=1)
>> Buffers: shared hit=3768
>> -> Gather (cost=10301.91..10302.14 rows=2 width=8) (actual time=43.125..47.544 rows=3 loops=1)
>> Workers Planned: 2
>> Workers Launched: 2
>> Buffers: shared hit=3768
>> -> Partial Aggregate (cost=9301.91..9301.94 rows=1 width=8) (actual time=27.190..27.190 rows=1 loops=3)
>> Buffers: shared hit=3768
>> -> Parallel Bitmap Heap Scan on treenode_edge te (cost=693.46..9299.66 rows=899 width=0) (actual time=4.058..27.175 rows=48 loops=3)
>> Recheck Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
>> Filter: ((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: 10800
>> Heap Blocks: exact=2171
>> Buffers: shared hit=3768
>> -> Bitmap Index Scan on treenode_edge_2d_gist (cost=0.00..692.92 rows=32352 width=0) (actual time=5.430..5.430 rows=32543 loops=1)
>> Index Cond: (edge && '0103000080010000000500000000000000A0C71C410000000020B51141000000008094034100000000A0C71C4100000000C0261441000000008094034100000000208E204100000000C026144100000000C095034100000000208E20410000000020B5114100000000C095034100000000A0C71C410000000020B511410000000080940341'::geometry)
>> Buffers: shared hit=261
>> Planning Time: 22.836 ms
>> Execution Time: 47.885 ms
>>
>> >We are definitely seeing different plans in that CTE.
>>
>> Out of curiosity, what is the plan you are seeing?
>>
>> >Testing for 12/3:
>> >
>> >12/3 is picking an index scan on the spatial index, which is turning out to be slower.
>> >You can adjust the COST of the ST_3DDWithin down: default cost is 10000.
>> >Where does the plan change as you adjust it down?
>> >ALTER FUNCTION ST_3DDWithin COST 9000;
>>
>> Once I set it to 608 and lower, this is what I get:
>>
>> Aggregate (cost=59.66..59.69 rows=1 width=8) (actual time=1.436..1.436 rows=1 loops=1)
>> Buffers: shared hit=183
>> -> Bitmap Heap Scan on treenode_edge te (cost=2.18..59.58 rows=33 width=0) (actual time=1.055..1.418 rows=143 loops=1)
>> Filter: ((project_id = 1) AND st_3ddwithin(edge, '0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
>> Heap Blocks: exact=119
>> Buffers: shared hit=183
>> -> Bitmap Index Scan on treenode_edge_gix (cost=0.00..2.17 rows=19 width=0) (actual time=1.027..1.027 rows=143 loops=1)
>> Index Cond: (edge &&& st_expand('0103000080010000000500000000000000F0C71C410000000070B51141000000002095034100000000F88D20410000000070B51141000000002095034100000000F88D20410000000070261441000000002095034100000000F0C71C410000000070261441000000002095034100000000F0C71C410000000070B511410000000020950341'::geometry, '20'::double precision))
>> Buffers: shared hit=64
>> Planning Time: 0.703 ms
>> Execution Time: 1.516 ms
>>
>> The planner doesn't come up with any other plan if I set it to anything
>> lower than 608.
>>
>> >A big change between 2.5 and 3.0 was costing on spatial functions, so
>> >seeing different plans is not surprising, especially since the
>> >mechanisms for building the plan have changed so much (SQL wrapper
>> >functions vs Pg12 support functions). It’s possible that with the cost
>> >of the function set so high, and actually being seen by the planner
>> >now, we’re getting an index scan that, in this case, is less efficient
>> >than the alternative.
>>
>> I see, thanks for the explanation. In my the 12/3 version of my
>> production database (50 million edges), I can't reproduce the cost based
>> plan change from above though. No matter how low I set the function cost
>> there, I always get the spatial scan in the plan.
>>
>> Cheers,
>> Tom
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>_______________________________________________
>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