[postgis-users] Fw: PGSQL 10.9 vs PGSQL 9.6 SQL query dirrent result when using GIS PGspehere indexes

[Ghiurea, Isabella]

________________________________


Hi List

we are facing some  issues when running   same SQL in different  PG version   using

GIS spatial index  aka PGsphere pos index in   PG SQL 10.9 vs 9.5.16 .

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

Here is in PG9.5.16

and pgsphere-1.1.aaf2d56-000.x86_64

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)}';
                                                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1358997.31..1358997.32 rows=1 width=16) (actual time=52963.730..52963.730 rows=1 loops=1)
   ->  Bitmap Heap Scan on dr1  (cost=16178.35..1345855.58 rows=404361 width=16) (actual time=411.320..51291.281 rows=1255823 loops=1)
         Recheck Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.591666616426078)}.
.'::spoly)
         Rows Removed by Index Recheck: 150145
         Heap Blocks: exact=38113
         ->  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)
               Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164260.
.78)}'::spoly)
 Planning time: 3.096 ms
 Execution time: 52964.699 ms
(9 rows)
#########################################################################

and PG10.9: with

pgsphere10-1.1.1-4.rhel7.x86_64


EXPLAIN ANALYZE SELECT count(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(long(pos))), max(degrees(lat(pos)))
[local]:5432 postgres at youcatdb-#FROMXXX.DR1  WHERE pos <@ spoly '{(189.1d,30.9d),(192.1d,30.9d),(192.1d,33.9d),(189.1d,33.9d)}';
                                                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=732312.22..732312.23 rows=1 width=40) (actual time=0.023..0.023 rows=1 loops=1)
   ->  Bitmap Heap Scan on dr1  (cost=9377.77..719172.89 rows=404287 width=16) (actual time=0.016..0.016 rows=0 loops=1)
         Recheck Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.591666616426078)}.
.'::spoly)
         ->  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)
               Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164260.
.78)}'::spoly)
 Planning time: 0.243 ms
 Execution time: 0.177 ms
(7 rows)




Any idea what can cause this ?

Thank you

Isabella

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20191021/0dbcfa73/attachment.html>