From: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
Date: Tue, 22 Oct 2019 16:07:58 +0000
Message-ID: <1571760478930.76745@nrc-cnrc.gc.ca> (raw)
In-Reply-To: <CAMkU=1xj5B8_TGRTSyc4ZKO4aPqR4PxmEpzVH0Ld1KrgyWT_EA@mail.gmail.com>
References: <1571677730032.95714@nrc-cnrc.gc.ca>
<CAMkU=1xj5B8_TGRTSyc4ZKO4aPqR4PxmEpzVH0Ld1KrgyWT_EA@mail.gmail.com>
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<mailto: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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Isabella.Ghiurea@nrc-cnrc.gc.ca, jeff.janes@gmail.com
Subject: Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
In-Reply-To: <1571760478930.76745@nrc-cnrc.gc.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox