On 9/9/25 7:25 PM, Paul Ramsey wrote:
On Mon, Sep 8, 2025 at 8:34 PM Sebastiaan Couwenberg via postgis-users <
postgis-users@lists.osgeo.org> wrote:
may be a regression in PostGIS 3.5.2, but I'm at a loss on how to confirm
and fix that.
So, this seems to be a change in planning behaviour pri
> On Sep 10, 2025, at 9:26 AM, Sebastiaan Couwenberg via postgis-users
> wrote:
>
> On 9/10/25 6:05 PM, Paul Ramsey wrote:
>> Because the core of your query is “find this small handful of records that
>> meet the unique key constraints, and then filter them with ST_Contains”, and
>> that qu
> Since the upgrade to Debian trixie with PostgreSQL 17.6 and PostGIS 3.5.2 the
> queries to fetch all ways connected to a boundary with nodes within a
> bounding box are terribly slow.
>
>
> osm-nl=# SELECT postgis_full_version();
>
> postgis_full_version
> -
> On 9/9/25 8:32 PM, Paul Ramsey wrote:
> > It’s a shame you cannot provide the selectivity on the other machine,
> because this way we are just guessing. The fact that selectivity is coming
> back
> at zero does maybe indicate a change in selectivity calculations, and zero is
> smaller than a lot
On 9/9/25 9:07 PM, Regina Obe wrote:
Can you get us a small dump of the data showing the issue or the recipe you
used to load the data?
The script to create the database is attached.
It assumes that the osmosis Debian package is installed and the non-standard
/var/tmp/osmosis directory exist
On 9/9/25 7:31 AM, Regina Obe wrote:
On 9/9/25 6:27 AM, Sebastiaan Couwenberg via postgis-users wrote:
I wasn't aware of the @ operator, looking at the documentation shouldn't the
equivalent of ST_Contains() be ~:
@ and ~ are commutators of each other, but neither is equivalent to ST_Contains
> As noted by Paul our selectivity is just screwed up and probably has been
> since 3.4.
> I'm still surprised though that it chose to use a GIST over an exact btree
> where
> your node.id is the primary key.
>
> I sadly stupidly dropped the gist index on mine to prove the point to myself
> that
On 9/9/25 4:14 PM, Daryl Herzmann wrote:
So I guess I
am at least curious what happens when you run this CTE example.
That's fast too:
osm-nl=# EXPLAIN (ANALYZE, BUFFERS) WITH mycte AS (SELECT wn.way_id, n.geom
FROM way_nodes AS wn, nodes AS n WHERE wn.node_id = 8646506472 AND wn.node_id =
Because the core of your query is “find this small handful of records that meet
the unique key constraints, and then filter them with ST_Contains”, and that
query cannot make use of the spatial index, so for that query the index is
superfluous. You might have other queries that *do* need the ind
On 9/10/25 6:05 PM, Paul Ramsey wrote:
Because the core of your query is “find this small handful of records that meet
the unique key constraints, and then filter them with ST_Contains”, and that
query cannot make use of the spatial index, so for that query the index is
superfluous. You might
On 9/11/25 5:36 AM, Sebastiaan Couwenberg via postgis-users wrote:
On 9/10/25 11:36 PM, Regina Obe wrote:
Never mind the test above I proposed, I tried it and it still decided to use
the spatial index instead of the primary key on n.id
Which I found really surprising as I thought primary keys t
On 9/10/25 11:36 PM, Regina Obe wrote:
Never mind the test above I proposed, I tried it and it still decided to use
the spatial index instead of the primary key on n.id
Which I found really surprising as I thought primary keys trump everything.
Anyway Paul traced the commit that caused the iss
> Dropping the index is not what you want, because you still want it for other
> queries. You do want a way to not use the index for this query where it's
> detrimental.
>
> Isn't the OFFSET 0 trick a way to manipulate the query planner to avoid using
> the detrimental index on its first try?
>
>
On 9/10/25 4:29 PM, Regina Obe wrote:
Thanks for the examples. I was able to use your script to load a database and
tested so far on with some pre-release variant of Trixie.
POSTGIS="3.6.0dev 3.6.0beta1-32-gd70143462" [EXTENSION] PGSQL="160" GEOS="3.14.0dev-CAPI-1.20.0" PROJ="9.6.0
NETWORK_EN
On 9/9/25 8:32 PM, Paul Ramsey wrote:
It’s a shame you cannot provide the selectivity on the other machine, because
this way we are just guessing. The fact that selectivity is coming back at zero
does maybe indicate a change in selectivity calculations, and zero is smaller
than a lot of number
> There is only a single version of postgresql & postgis in the Debian stable
> repos, pgdg packages are not used.
>
> > - Can you check the PostGIS selectivity estimate on the old and the
> > new installations, using
> >
> > SELECT _postgis_selectivity ('nodes', 'geom',
> > ST_GeomFromEWKT('SRID
> On Sep 9, 2025, at 11:29 AM, Sebastiaan Couwenberg via postgis-users
> wrote:
>
> On 9/9/25 7:25 PM, Paul Ramsey wrote:
>> On Mon, Sep 8, 2025 at 8:34 PM Sebastiaan Couwenberg via postgis-users <
>> postgis-users@lists.osgeo.org> wrote:
>>> may be a regression in PostGIS 3.5.2, but I'm at a
On Mon, Sep 8, 2025 at 8:34 PM Sebastiaan Couwenberg via postgis-users <
postgis-users@lists.osgeo.org> wrote:
> may be a regression in PostGIS 3.5.2, but I'm at a loss on how to confirm
> and fix that.
>
>
So, this seems to be a change in planning behaviour primarily, and you have
a lot of varia
Greetings,
I suspect that my comment is not all that helpful, but this general
case of postgres preferring the much slower ST_Contains vs using the
first two query conditions and then applying ST_Contains is something
I have greatly struggled with over recent releases. My hacky solution
is to use
> On 9/9/25 6:27 AM, Sebastiaan Couwenberg via postgis-users wrote:
> > On 9/9/25 6:04 AM, Regina Obe wrote:
> >> To take GEOS out of the equation, what is performance if you just do below
> compared to your old performance
> >
> > That is significantly faster:
> >
> > osm-nl=# EXPLAIN (ANALYZE, BU
On 9/9/25 6:27 AM, Sebastiaan Couwenberg via postgis-users wrote:
On 9/9/25 6:04 AM, Regina Obe wrote:
To take GEOS out of the equation, what is performance if you just do below
compared to your old performance
That is significantly faster:
osm-nl=# EXPLAIN (ANALYZE, BUFFERS) SELECT wn.way_i
On 9/9/25 6:04 AM, Regina Obe wrote:
To take GEOS out of the equation, what is performance if you just do below
compared to your old performance
That is significantly faster:
osm-nl=# EXPLAIN (ANALYZE, BUFFERS) SELECT wn.way_id FROM way_nodes
AS wn, nodes AS n WHERE wn.node_id = 8646506472 A
On 9/9/25 6:04 AM, Regina Obe wrote:
Are you upgrading from 3.5.2 to 3.5.3 or from pre 3.5.1 to 3.5.2? sounds from
your below comment that is what you are doing, but you are showing 3.5.2 output
so not clear if that is your new setup or your old setup that was working.
What I'm showing is my
Since the upgrade to Debian trixie with PostgreSQL 17.6 and PostGIS 3.5.2 the
queries to fetch all ways connected to a boundary with nodes within a bounding
box are terribly slow.
osm-nl=# SELECT postgis_full_version();
24 matches
Mail list logo
