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 <l...@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 <wangdapeng20191...@gmail.com> > *Sent:* Tuesday, February 27, 2024 1:05 AM > *To:* Regina Obe <l...@pcorp.us> > *Cc:* PostGIS Users Discussion <postgis-users@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 <l...@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 <wangdapeng20191...@gmail.com> > *Sent:* Sunday, February 25, 2024 9:09 AM > *To:* Regina Obe <l...@pcorp.us> > *Cc:* PostGIS Users Discussion <postgis-users@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 <l...@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 <wangdapeng20191...@gmail.com> > *Sent:* Friday, February 23, 2024 8:25 PM > *To:* Regina Obe <l...@pcorp.us> > *Cc:* PostGIS Users Discussion <postgis-users@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 <l...@pcorp.us> 于2024年2月23日周五 10:23写道: > > You have an example query where you are seeing this behavior? > > > > *From:* Dapeng Wang <wangdapeng20191...@gmail.com> > *Sent:* Thursday, February 22, 2024 8:57 PM > *To:* PostGIS Users Discussion <postgis-users@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 > >