<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"MS Gothic";
panose-1:2 11 6 9 7 2 5 8 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@MS Gothic";
panose-1:2 11 6 9 7 2 5 8 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>But do the version() numbers agree?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>SELECT version();<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I recall that issue being in PostgreSQL itself. So a difference in version there might explain things.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The other place I’ve see that kind of issue is with foreign tables, but I assume you are not using any of those.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b>From:</b> Dapeng Wang <wangdapeng20191008@gmail.com> <br><b>Sent:</b> Sunday, February 25, 2024 9:09 AM<br><b>To:</b> Regina Obe <lr@pcorp.us><br><b>Cc:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: The intersect function does not utilize indexes<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>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. <br>Since it's a client development version of the database, any scenario could have occurred.<br>After identifying the cause, I will reply to the email.<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> <span style='font-family:"MS Gothic"'>于</span>2024<span style='font-family:"MS Gothic"'>年</span>2<span style='font-family:"MS Gothic"'>月</span>24<span style='font-family:"MS Gothic"'>日周六</span> 10:18<span style='font-family:"MS Gothic"'>写道：</span><o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Verify that<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT version();<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Returns the same or higher version number on your windows system than your Linux.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Also make sure<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT postgis_full_version(); <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Doesn’t say you need updates. If it does make sure to run<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT postgis_extensions_upgrade();<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Dapeng Wang <<a href="mailto:wangdapeng20191008@gmail.com" target="_blank">wangdapeng20191008@gmail.com</a>> <br><b>Sent:</b> Friday, February 23, 2024 8:25 PM<br><b>To:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Cc:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: The intersect function does not utilize indexes<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The executed SQL is very simple, and I executed the command explain (analyze, Buffers, verbose) select <a href="http://a.id" target="_blank">a.id</a> from random_2point_lines a join t1 b on public.st_intersects(a.geom, b.geom);. <br>While printing the planner's data in the PostgreSQL database, I encountered an error message "cache lookup failed for operator family 0."<br>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.<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> <span style='font-family:"MS Gothic"'>于</span>2024<span style='font-family:"MS Gothic"'>年</span>2<span style='font-family:"MS Gothic"'>月</span>23<span style='font-family:"MS Gothic"'>日周五</span> 10:23<span style='font-family:"MS Gothic"'>写道：</span><o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>You have an example query where you are seeing this behavior?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Dapeng Wang <<a href="mailto:wangdapeng20191008@gmail.com" target="_blank">wangdapeng20191008@gmail.com</a>> <br><b>Sent:</b> Thursday, February 22, 2024 8:57 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> The intersect function does not utilize indexes<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hello everyone,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<br><br>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.<br><br>Here's what I've investigated so far (using the same DDL):<br>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.<br>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.<br>Vacuum analyze has been performed, and I've attempted to disable sequential scans and force index scans, but to no avail.<br>Do you have any good suggestions?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Dapeng<o:p></o:p></p></div></div></div></div></div></div></blockquote></div></div></div></div></div></blockquote></div></div></div></body></html>