Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMbB0-0006QU-U6 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Oct 2019 17:09:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMbAz-0008U8-N3 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Oct 2019 17:09:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMbAz-0008T8-5h for pgsql-admin@lists.postgresql.org; Mon, 21 Oct 2019 17:09:01 +0000 Received: from mx.ssan.egs-seg.gc.ca ([205.193.229.132]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1iMbAt-0004qn-Di for pgsql-admin@postgresql.org; Mon, 21 Oct 2019 17:08:57 +0000 Received: from DC01ZWH0021.corp.nrc.gc.ca ([167.37.58.17]) by mx.ssan.egs-seg.gc.ca with ESMTP id x9LH8oNK013649-x9LH8oNM013649 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA bits=256 verify=CAFAIL) for ; Mon, 21 Oct 2019 13:08:50 -0400 Received: from DC01ZWH0016.Corp.nrc.gc.ca (167.37.54.210) by DC01ZWH0021.Corp.nrc.gc.ca (167.37.58.17) with Microsoft SMTP Server (TLS) id 15.0.1347.2; Mon, 21 Oct 2019 13:08:48 -0400 Received: from DC01ZWH0014.Corp.nrc.gc.ca (167.37.54.208) by DC01ZWH0016.Corp.nrc.gc.ca (167.37.54.210) with Microsoft SMTP Server (TLS) id 15.0.1473.3; Mon, 21 Oct 2019 13:08:50 -0400 Received: from DC01ZWH0014.Corp.nrc.gc.ca ([192.168.111.100]) by DC01ZWH0014.Corp.nrc.gc.ca ([192.168.111.100]) with mapi id 15.00.1473.003; Mon, 21 Oct 2019 13:08:50 -0400 From: "Ghiurea, Isabella" To: "pgsql-admin@postgresql.org" Subject: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour. Thread-Topic: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour. Thread-Index: AQHViCfpKAHNWn5Whkmix1SRNDN3wg== Date: Mon, 21 Oct 2019 17:08:50 +0000 Message-ID: <1571677730032.95714@nrc-cnrc.gc.ca> Accept-Language: en-US, en-CA Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [167.37.48.8] Content-Type: multipart/alternative; boundary="_000_157167773003295714nrccnrcgcca_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_157167773003295714nrccnrcgcca_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi List we are facing some performance issues with same SQL running in different v= ersion ( PG SQL 10.9 vs 9.5.16)returning different results when using GIST spatial index see bell= ow , In PG 9,5.16 we are seeing the correct/expecting number of rows returned BU= T 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=3D1358997.31..1358997.32 rows=3D1 width=3D16) (actual tim= e=3D52963.730..52963.730 rows=3D1 loops=3D1) -> Bitmap Heap Scan on dr1 (cost=3D16178.35..1345855.58 rows=3D404361 = width=3D16) (actual time=3D411.320..51291.281 rows=3D1255823 loops=3D1) 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=3D38113 -> Bitmap Index Scan on i_dr1_pos (cost=3D0.00..16077.26 rows=3D= 404361 width=3D0) (actual time=3D402.564..402.564 rows=3D1405968 loops=3D1) Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248)= ,(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.5916666164260= 78),(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,3= 0.9d),(192.1d,30.9d),(192.1d,33.9d),(189.1d,33.9d)}'; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- Aggregate (cost=3D732312.22..732312.23 rows=3D1 width=3D40) (actual time= =3D0.023..0.023 rows=3D1 loops=3D1) -> Bitmap Heap Scan on dr1 (cost=3D9377.77..719172.89 rows=3D404287 wi= dth=3D16) (actual time=3D0.016..0.016 rows=3D0 loops=3D1) 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=3D0.00..9276.70 rows=3D4= 04287 width=3D0) (actual time=3D0.012..0.012 rows=3D0 loops=3D1) Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248)= ,(3.35277749308111 , 0.539306738866248),(3.35277749308111 , 0.5916666164260= 78),(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 --_000_157167773003295714nrccnrcgcca_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi List

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

9.5.16)returning  different results when using GIST spatial index &= nbsp;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(lo= ng(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)}';
            &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;  QUERY PLAN         &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;     
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------
 Aggregate  (cost=3D1358997.31..1358997.32 rows=3D1 width=3D16) (= actual time=3D52963.730..52963.730 rows=3D1 loops=3D1)
   ->  Bitmap Heap Scan on dr1  (cost=3D16178.35..13= 45855.58 rows=3D404361 width=3D16) (actual time=3D411.320..51291.281 rows= =3D1255823 loops=3D1)
         Recheck Cond: (pos <@ '= {(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.5393067388662= 48),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164= 26078)}.
.'::spoly)
         Rows Removed by Index Rech= eck: 150145
         Heap Blocks: exact=3D38113=
         ->  Bitmap Index S= can on i_dr1_pos  (cost=3D0.00..16077.26 rows=3D404361 width=3D0) (act= ual time=3D402.564..402.564 rows=3D1405968 loops=3D1)
            &nb= sp;  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(lo= ng(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)}';
            &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;  QUERY PLAN         &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;     
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------
 Aggregate  (cost=3D732312.22..732312.23 rows=3D1 width=3D40) (ac= tual time=3D0.023..0.023 rows=3D1 loops=3D1)
   ->  Bitmap Heap Scan on dr1  (cost=3D9377.77..719= 172.89 rows=3D404287 width=3D16) (actual time=3D0.016..0.016 rows=3D0 loops= =3D1)
         Recheck Cond: (pos <@ '= {(3.30041761552128 , 0.539306738866248),(3.35277749308111 , 0.5393067388662= 48),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , 0.5916666164= 26078)}.
.'::spoly)
         ->  Bitmap Index S= can on i_dr1_pos  (cost=3D0.00..9276.70 rows=3D404287 width=3D0) (actu= al time=3D0.012..0.012 rows=3D0 loops=3D1)
            &nb= sp;  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


--_000_157167773003295714nrccnrcgcca_--