Thread: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

From
"Ghiurea, Isabella"
Date:

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


Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

From
Tom Lane
Date:
"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



Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

From
Jeff Janes
Date:
On Mon, Oct 21, 2019 at 1:09 PM Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca> wrote:

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.


If you disable the index (`set enable_bitmapscan=off` should do that) do you get the same answer on each server?

Cheer,

Jeff
 

Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

From
"Ghiurea, Isabella"
Date:

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)


From: Jeff Janes <jeff.janes@gmail.com>
Sent: Monday, October 21, 2019 3:20 PM
To: Ghiurea, Isabella
Cc: pgsql-admin@postgresql.org
Subject: Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
 
On Mon, Oct 21, 2019 at 1:09 PM Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca> wrote:

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.


If you disable the index (`set enable_bitmapscan=off` should do that) do you get the same answer on each server?

Cheer,

Jeff
 

Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

From
Jeff Janes
Date:
On Tue, Oct 22, 2019 at 12:08 PM Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca> wrote:

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),


If you recreate the index, is it still broken or does it work properly?
 
Cheers,

Jeff