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

[Regina Obe]

Isabella,

 

At a glance given the           Rows Removed by Index Recheck: 150145 

Count being so high relative to your 9.5, I would suspect something is wrong
with your spatial index.

 

Have you tried reindexing the table in question.  

REINDEX TABLE xxx.DR1

 

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.

 

BTW this is the PostGIS list, not pgSphere, but perhaps PostGIS is the
closest to a pgSphere mailing list there is.

 

Hope that helps,

Regina

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Ghiurea, Isabella
Sent: Monday, October 21, 2019 3:39 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Fw: PGSQL 10.9 vs PGSQL 9.6 SQL query dirrent
result when using GIS PGspehere indexes

 

 

  _____  

 

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
<mailto: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/c8c7b406/attachment.html>