<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=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@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;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@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 bgcolor=white lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Isabella,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>At a glance given the </span><span style='font-family:"Calibri",sans-serif;color:black'> Rows Removed by Index Recheck: 150145 <o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Count being so high relative to your 9.5, I would suspect something is wrong with your spatial index.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Have you tried reindexing the table in question. <o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>REINDEX TABLE xxx.DR1<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>I think a lot of things have happened (not sure about pgSphere) with how gist and spgist indexes are handled that caused some issues for folks using gist and spgist. Reindexing often fixed those issues.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>BTW this is the PostGIS list, not pgSphere, but perhaps PostGIS is the closest to a pgSphere mailing list there is.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Regina</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Ghiurea, Isabella<br><b>Sent:</b> Monday, October 21, 2019 3:39 PM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] Fw: PGSQL 10.9 vs PGSQL 9.6 SQL query dirrent result when using GIS PGspehere indexes<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><div><div class=MsoNormal align=center style='text-align:center;background:white'><span style='font-family:"Calibri",sans-serif;color:black'><hr size=3 width="98%" align=center></span></div><div id=divRplyFwdMsg><div><p class=MsoNormal style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'> <o:p></o:p></span></p></div></div><div><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>Hi List <o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>we are facing some issues when running same SQL in different PG version using<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>GIS spatial index aka PGsphere pos index in PG SQL 10.9 vs 9.5.16 .<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>In PG 9,5.16 we are seeing the correct/expecting number of rows returned BUT in in PG 10.9 we are seeing 0 rows been returned<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>Here is in PG9.5.16<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>and pgsphere-1.1.aaf2d56-000.x86_64<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>EXPLAIN ANALYZE SELECT count(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(long(pos))), max(degrees(lat(pos)))FROM xxx.DR1 WHERE pos <@ spoly '{(189.1d,30.9d),(192.1d,30.9d),(192.1d,33.9d),(189.1d,33.9d)}';<br> QUERY PLAN <br>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> Aggregate (cost=1358997.31..1358997.32 rows=1 width=16) (actual time=52963.730..52963.730 rows=1 loops=1)<br> -> Bitmap Heap Scan on dr1 (cost=16178.35..1345855.58 rows=404361 width=16) (actual time=411.320..51291.281 rows=1255823 loops=1)<br> Recheck Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.591666616426078)}.<br>.'::spoly)<br> Rows Removed by Index Recheck: 150145<br> Heap Blocks: exact=38113<br> -> Bitmap Index Scan on i_dr1_pos (cost=0.00..16077.26 rows=404361 width=0) (actual time=402.564..402.564 rows=1405968 loops=1)<br> Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164260.<br>.78)}'::spoly)<br> Planning time: 3.096 ms<br> Execution time: 52964.699 ms<br>(9 rows)<br>#########################################################################<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>and PG10.9: with <o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>pgsphere10-1.1.1-4.rhel7.x86_64<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>EXPLAIN ANALYZE SELECT count(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(long(pos))), max(degrees(lat(pos)))<br>[local]:5432 <a href="mailto:postgres@youcatdb-#FROMXXX.DR1">postgres@youcatdb-#FROMXXX.DR1</a> WHERE pos <@ spoly '{(189.1d,30.9d),(192.1d,30.9d),(192.1d,33.9d),(189.1d,33.9d)}';<br> QUERY PLAN <br>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> Aggregate (cost=732312.22..732312.23 rows=1 width=40) (actual time=0.023..0.023 rows=1 loops=1)<br> -> Bitmap Heap Scan on dr1 (cost=9377.77..719172.89 rows=404287 width=16) (actual time=0.016..0.016 rows=0 loops=1)<br> Recheck Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.591666616426078)}.<br>.'::spoly)<br> -> Bitmap Index Scan on i_dr1_pos (cost=0.00..9276.70 rows=404287 width=0) (actual time=0.012..0.012 rows=0 loops=1)<br> Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164260.<br>.78)}'::spoly)<br> Planning time: 0.243 ms<br> Execution time: 0.177 ms<br>(7 rows)<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>Any idea what can cause this ?<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>Thank you<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'>Isabella<o:p></o:p></span></p><p style='background:white'><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div></div></div></body></html>