pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
5+ messages / 3 participants
[nested] [flat]
* PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
@ 2019-10-21 17:08 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Ghiurea, Isabella @ 2019-10-21 17:08 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
@ 2019-10-21 19:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2019-10-21 19:18 UTC (permalink / raw)
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
@ 2019-10-21 22:20 Jeff Janes <jeff.janes@gmail.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 1 reply; 5+ messages in thread
From: Jeff Janes @ 2019-10-21 22:20 UTC (permalink / raw)
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
@ 2019-10-22 16:07 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ghiurea, Isabella @ 2019-10-22 16:07 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour.
@ 2019-10-22 16:27 Jeff Janes <jeff.janes@gmail.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2019-10-22 16:27 UTC (permalink / raw)
To: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-10-22 16:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-21 17:08 PGSQL 10.9 vs PGSQL 9.6 SQL query behaviour. Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
2019-10-21 19:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-10-21 22:20 ` Jeff Janes <jeff.janes@gmail.com>
2019-10-22 16:07 ` Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
2019-10-22 16:27 ` Jeff Janes <jeff.janes@gmail.com>
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