Обсуждение: 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@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

"Ghiurea, Isabella" <Isabella.Ghiurea@nrc-cnrc.gc.ca> writes:
> 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 ,

Getting the wrong answer isn't a "performance issue" :-(

You might find that reindexing the index makes the problem go away.
Otherwise, I'd suggest inquiring in the PostGIS mailing list whether
there's any known bugs in their code for 10.x.  I don't see anything
in our commit logs suggesting that there's a known problem with the
core GIST code, but maybe there's something wrong with the PostGIS
operator class the index is using.

            regards, tom lane

Hi  Jeff,

Thank you for suggestion, yes we tested without Pgsphere index ( dropped the index) in PGSQL 10.9 , run same SQL and got the expected result which is same as the one in PG 9.5 but different f when using the PG sphere in 10.9( which returned null rows),

explain SELECT count(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(lat(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=13576821.96..13576821.97 rows=1 width=40)
   ->  Seq Scan on DR1  (cost=0.00..13563681.20 rows=404331 width=16)
         Filter: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.59166
6616426078),(3.30041761552128 , 0.591666616426078)}'::spoly)