PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.

Поиск
Список
Период
Сортировка
От Ghiurea, Isabella
Тема PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
Дата
Msg-id 1571677730032.95714@nrc-cnrc.gc.ca
обсуждение исходный текст
Ответы Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-admin

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


В списке pgsql-admin по дате отправления:

Предыдущее
От: ihaider@gmx.com
Дата:
Сообщение: Re: pgAgent forces postgresql-9.4
Следующее
От: Tom Lane
Дата:
Сообщение: Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.