The intersect function does not utilize indexes

[Dapeng Wang]

Thank you very much, I think this is sufficient.

I found https://github.com/pramsey/postgis-build-windows written by
pramsey, which is very helpful for compiling on Windows.
I will share any new progress.

As a tip, if you encounter "cache lookup failed for operator family 0."
again, you can look at the enumerations defined in nodes.h. This is a very
good method.

Regina Obe <lr at pcorp.us> 于2024年2月27日周二 14:58写道：

> Dapeng,
>
>
>
> Is there a reason they found a need to hack their PostgreSQL 12.  Can they
> maybe use a newer version of PostgreSQL for their windows testing.
>
>
>
> I haven’t had time to update instructions for building on windows.  It’s
> very complicated with all the dependencies needed.
>
>
>
> The steps our windows ci uses for building are here, but it’s very path
> based on how the windows Jenkins build bot is configured and my current
> mingw64 build chain
>
>
>
>
> https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/ci/winnie/build_postgis.sh
>
>
>
> But requires a mingw64 configured.  Currently I’m using gcc 8.1.0, but in
> middle of upgrading to a newer GCC.  So that’s what my focus has been on
> right now and once I have that working, I’ll have build instructions for
> that.  But I’ve still got a ways to go.
>
>
>
> Thanks,
>
> Regina
>
>
>
>
>
>
>
> *From:* Dapeng Wang <wangdapeng20191008 at gmail.com>
> *Sent:* Tuesday, February 27, 2024 1:05 AM
> *To:* Regina Obe <lr at pcorp.us>
> *Cc:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: The intersect function does not utilize indexes
>
>
>
> I am very happy to have found the reason.
>
> The client's development database is based on pg12. They modified the
> nodes.h file and added two enumerations above
> T_SupportRequestIndexCondition. Since the client downloaded pg12-postgis
> from postgis.org, the installation file is based on the pg12 version, and
> the corresponding .h file value is 416. However, the client's version is
> 418 (because they customized two enumerations). Adjusting the order of the
> enumerations and recompiling should resolve the issue.
>
> I would like to ask, how to compile postgis on Windows, and if you can
> provide a batch file or instructions.
>
> Thanks,
> Dapeng
>
>
>
> Regina Obe <lr at pcorp.us> 于2024年2月26日周一 03:06写道：
>
> But do the version()  numbers agree?
>
>
>
> SELECT version();
>
>
>
> I recall that issue being in PostgreSQL itself.  So a difference in
> version there might explain things.
>
>
>
> The other place I’ve see that kind of issue is with foreign tables, but I
> assume you are not using any of those.
>
>
>
> *From:* Dapeng Wang <wangdapeng20191008 at gmail.com>
> *Sent:* Sunday, February 25, 2024 9:09 AM
> *To:* Regina Obe <lr at pcorp.us>
> *Cc:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: The intersect function does not utilize indexes
>
>
>
> The version installed is 3.4.1, which is already the latest version. I'll
> navigate upwards from the "Cache Lookup Error" location to identify the
> cause.
> Since it's a client development version of the database, any scenario
> could have occurred.
> After identifying the cause, I will reply to the email.
>
>
>
> Regina Obe <lr at pcorp.us> 于2024年2月24日周六 10:18写道：
>
> I’ve seen that cached lookup error before but can’t recall when.  It might
> have been a bug in a micro release fixed in a later.
>
>
>
> Verify that
>
>
>
> SELECT version();
>
>
>
> Returns the same or higher version number on your windows system than your
> Linux.
>
>
>
> Also make sure
>
>
>
> SELECT postgis_full_version();
>
>
>
> Doesn’t say you need updates.  If it does make sure to run
>
>
>
> SELECT postgis_extensions_upgrade();
>
>
>
>
>
>
>
> *From:* Dapeng Wang <wangdapeng20191008 at gmail.com>
> *Sent:* Friday, February 23, 2024 8:25 PM
> *To:* Regina Obe <lr at pcorp.us>
> *Cc:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: The intersect function does not utilize indexes
>
>
>
> The executed SQL is very simple, and I executed the command explain
> (analyze, Buffers, verbose) select a.id from random_2point_lines a join
> t1 b on public.st_intersects(a.geom, b.geom);.
> While printing the planner's data in the PostgreSQL database, I
> encountered an error message "cache lookup failed for operator family 0."
> In a PostgreSQL database, it would calculate the time for sequential scans
> and index scans to choose the one with minimal time consumption. However,
> in the customer's development database, there was an error encountered
> while scanning the index, displaying the mentioned error message.
>
>
>
> Regina Obe <lr at pcorp.us> 于2024年2月23日周五 10:23写道：
>
> You have an example query where you are seeing this behavior?
>
>
>
> *From:* Dapeng Wang <wangdapeng20191008 at gmail.com>
> *Sent:* Thursday, February 22, 2024 8:57 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* The intersect function does not utilize indexes
>
>
>
> Hello everyone,
>
>
>
> I have a client with a database developed on PostgreSQL 12, with both
> Windows and Linux versions. Everything is normal with the Linux version,
> but there are anomalies with the Windows version.
>
> While the Windows database can have the PostGIS extension installed,
> create indexes, and execute SQL queries correctly, spatial functions do not
> utilize indexes, such as intersection and containment.
>
> Here's what I've investigated so far (using the same DDL):
> On the Windows system, PostgreSQL 12 is installed along with PostGIS
> versions 3.3.0 to 3.4.1, and everything works fine. Spatial functions can
> utilize indexes.
> On the Windows system, the client's developed database is installed with
> PostGIS versions 3.3.0 to 3.4.1. Queries using the && operator utilize
> indexes, but spatial functions (such as st_intersects and ST_Contains) do
> not utilize indexes.
> Vacuum analyze has been performed, and I've attempted to disable
> sequential scans and force index scans, but to no avail.
> Do you have any good suggestions?
>
>
>
> Thanks,
>
> Dapeng
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20240227/32f7edd3/attachment.htm>