The intersect function does not utilize indexes

[Dapeng Wang]

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/20240225/9f6ba6e7/attachment.htm>