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 1iMwhr-0000lR-EB for pgsql-admin@arkaria.postgresql.org; Tue, 22 Oct 2019 16:08:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMwho-00084j-2f for pgsql-admin@arkaria.postgresql.org; Tue, 22 Oct 2019 16:08:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMwhn-00080D-2w for pgsql-admin@lists.postgresql.org; Tue, 22 Oct 2019 16:08:19 +0000 Received: from mx.ssan.egs-seg.gc.ca ([205.193.229.132]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMwhY-0006NG-4G for pgsql-admin@postgresql.org; Tue, 22 Oct 2019 16:08:13 +0000 Received: from DC01ZWH0020.paz.nrc.gc.ca ([167.37.58.16]) by mx.ssan.egs-seg.gc.ca with ESMTP id x9MG7xaA030060-x9MG7xaC030060 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA bits=256 verify=CAFAIL); Tue, 22 Oct 2019 12:07:59 -0400 Received: from DC01ZWH0011.Corp.nrc.gc.ca (167.37.54.205) by DC01ZWH0020.paz.nrc.gc.ca (167.37.58.16) with Microsoft SMTP Server (TLS) id 15.0.1347.2; Tue, 22 Oct 2019 12:07:57 -0400 Received: from DC01ZWH0014.Corp.nrc.gc.ca (167.37.54.208) by DC01ZWH0011.Corp.nrc.gc.ca (167.37.54.205) with Microsoft SMTP Server (TLS) id 15.0.1473.3; Tue, 22 Oct 2019 12:07:59 -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; Tue, 22 Oct 2019 12:07:59 -0400 From: "Ghiurea, Isabella" To: Jeff Janes CC: "pgsql-admin@postgresql.org" Subject: Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour. Thread-Topic: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour. Thread-Index: AQHViCfpKAHNWn5Whkmix1SRNDN3wqdl7iiAgADlD90= Date: Tue, 22 Oct 2019 16:07:58 +0000 Message-ID: <1571760478930.76745@nrc-cnrc.gc.ca> References: <1571677730032.95714@nrc-cnrc.gc.ca>, In-Reply-To: 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_157176047893076745nrccnrcgcca_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_157176047893076745nrccnrcgcca_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Jeff, Thank you for suggestion, yes we tested without Pgsphere index ( dropped th= e 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=3D13576821.96..13576821.97 rows=3D1 width=3D40) -> Seq Scan on DR1 (cost=3D0.00..13563681.20 rows=3D404331 width=3D16) Filter: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.352777= 49308111 , 0.539306738866248),(3.35277749308111 , 0.59166 6616426078),(3.30041761552128 , 0.591666616426078)}'::spoly) ________________________________ From: Jeff Janes 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 > wrote: 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. If you disable the index (`set enable_bitmapscan=3Doff` should do that) do = you get the same answer on each server? Cheer, Jeff --_000_157176047893076745nrccnrcgcca_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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

<= /p> 

explain SELECT cou=
nt(*), min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(lat(po=
s))), 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;            =
 QUERY PLAN                       &=
nbsp;       
    =
             &n=
bsp;            =
;                        =
       
----------------------------------=
---------------------------------------------------------------------------=
-----------------------
------------------------------------------------=
-------------
 Aggregate  (cost=3D13576821.96..13576821.97 row=
s=3D1 width=3D40)
   ->  Seq Scan on DR1  (cost=3D0.00..13563681.20 rows=3D404331 width=3D16)
   &nbs=
p;     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=3Doff` should do that=
) do you get the same answer on each server?







Cheer,







Jeff


 












--_000_157176047893076745nrccnrcgcca_--