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