PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

Hi List

we are facing some performance issues with same SQL running in different version ( PG SQL 10.9 vs

9.5.16)returning different results when using GIST spatial index see bellow ,

In PG 9,5.16 we are seeing the correct/expecting number of rows returned BUT in in PG 10.9 we are seeing returns 0 rows.

Here is in PG9.5.16

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:

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(dot)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