<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none"><!--P{margin-top:0;margin-bottom:0;} p
{margin-top:0;
margin-bottom:0}--></style>
</head>
<body dir="ltr" style="font-size:12pt;color:#000000;background-color:#FFFFFF;font-family:Calibri,Arial,Helvetica,sans-serif;">
<p><br>
</p>
<div dir="ltr" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b></b></font>
<div> </div>
</div>
<div>
<p>Hi List </p>
<p>we are facing some issues when running same SQL in different PG version using</p>
<p>GIS spatial index aka PGsphere pos index in PG SQL 10.9 vs 9.5.16 .</p>
<p>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<br>
</p>
<p>Here is in PG9.5.16</p>
<p>and pgsphere-1.1.aaf2d56-000.x86_64<br>
</p>
<p>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>
#########################################################################</p>
<p>and PG10.9: with </p>
<p>pgsphere10-1.1.1-4.rhel7.x86_64</p>
<p><br>
</p>
<p>EXPLAIN ANALYZE SELECT count(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(long(pos))), max(degrees(lat(pos)))<br>
[local]:5432 postgres@youcatdb-#FROMXXX.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=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)</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p>Any idea what can cause this ?</p>
<p>Thank you</p>
<p>Isabella<br>
</p>
<p><br>
</p>
</div>
</div>
</body>
</html>