[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Fw: PGSQL 10.9 vs PGSQL 9.6 SQL query dirrent result when using GIS PGspehere in
From: "Regina Obe" <lr () pcorp ! us>
Date: 2019-10-21 21:09:57
Message-ID: 000b01d58853$e5b00980$b1101c80$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
Isabella,
At a glance given the Rows Removed by Index Recheck: 150145
Count being so high relative to your 9.5, I would suspect something is wrong
with your spatial index.
Have you tried reindexing the table in question.
REINDEX TABLE xxx.DR1
I think a lot of things have happened (not sure about pgSphere) with how
gist and spgist indexes are handled that caused some issues for folks using
gist and spgist. Reindexing often fixed those issues.
BTW this is the PostGIS list, not pgSphere, but perhaps PostGIS is the
closest to a pgSphere mailing list there is.
Hope that helps,
Regina
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf
Of Ghiurea, Isabella
Sent: Monday, October 21, 2019 3:39 PM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Fw: PGSQL 10.9 vs PGSQL 9.6 SQL query dirrent
result when using GIS PGspehere indexes
_____
Hi List
we are facing some issues when running same SQL in different PG version
using
GIS spatial index aka PGsphere pos index in PG SQL 10.9 vs 9.5.16 .
In PG 9,5.16 we are seeing the correct/expecting number of rows returned BUT
in in PG 10.9 we are seeing 0 rows been returned
Here is in PG9.5.16
and pgsphere-1.1.aaf2d56-000.x86_64
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: with
pgsphere10-1.1.1-4.rhel7.x86_64
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
<mailto: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
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 \
(filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);} o\:* \
{behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body bgcolor=white lang=EN-US \
link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Isabella,<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>At a glance \
given the </span><span \
style='font-family:"Calibri",sans-serif;color:black'> \
Rows Removed by Index Recheck: 150145 <o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'>Count being so high relative to \
your 9.5, I would suspect something is wrong with your spatial \
index.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Have you \
tried reindexing the table in question. <o:p></o:p></span></p><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>REINDEX \
TABLE xxx.DR1<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>I think a \
lot of things have happened (not sure about pgSphere) with how gist and spgist \
indexes are handled that caused some issues for folks using gist and spgist. \
Reindexing often fixed those issues.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>BTW this \
is the PostGIS list, not pgSphere, but perhaps PostGIS is the closest to a pgSphere \
mailing list there is.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Hope that \
helps,<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-family:"Calibri",sans-serif;color:black'>Regina</span><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Ghiurea, \
Isabella<br><b>Sent:</b> Monday, October 21, 2019 3:39 PM<br><b>To:</b> \
postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] Fw: PGSQL 10.9 vs \
PGSQL 9.6 SQL query dirrent result when using GIS PGspehere \
indexes<o:p></o:p></span></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><p><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><div><div \
class=MsoNormal align=center style='text-align:center;background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><hr size=3 width="98%" \
align=center></span></div><div id=divRplyFwdMsg><div><p class=MsoNormal \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'> <o:p></o:p></span></p></div></div><div><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>Hi List <o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>we are facing some issues \
when running same SQL in different PG version \
using<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>GIS spatial index aka \
PGsphere pos index in PG SQL 10.9 vs \
9.5.16 .<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>In PG 9,5.16 we are seeing the \
correct/expecting number of rows returned BUT in in PG 10.9 we are seeing 0 \
rows been returned<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>Here is in \
PG9.5.16<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>and \
pgsphere-1.1.aaf2d56-000.x86_64<o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>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)}';<br> \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
QUERY PLAN &nbs \
p; \
&n \
bsp; &nbs \
p; \
\
<br>---------------------------------------------------------------------------- \
------------------------------------------------------------------------------------------------------------------<br> Aggregate \
(cost=1358997.31..1358997.32 rows=1 width=16) (actual time=52963.730..52963.730 \
rows=1 loops=1)<br> -> Bitmap Heap Scan on dr1 \
(cost=16178.35..1345855.58 rows=404361 width=16) (actual time=411.320..51291.281 \
rows=1255823 loops=1)<br> Recheck \
Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , \
0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , \
0.591666616426078)}.<br>.'::spoly)<br> \
Rows Removed by Index Recheck: \
150145<br> Heap Blocks: \
exact=38113<br> -> 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)<br> \
Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , \
0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , \
0.5916666164260.<br>.78)}'::spoly)<br> Planning time: 3.096 \
ms<br> Execution time: 52964.699 ms<br>(9 \
rows)<br>#########################################################################<o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>and PG10.9: with \
<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>pgsphere10-1.1.1-4.rhel7.x86_64<o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>EXPLAIN ANALYZE SELECT count(*), \
min(degrees(long(pos))), max(degrees(long(pos))), min(degrees(long(pos))), \
max(degrees(lat(pos)))<br>[local]:5432 <a \
href="mailto:postgres@youcatdb-#FROMXXX.DR1">postgres@youcatdb-#FROMXXX.DR1</a> \
WHERE pos <@ spoly \
'{(189.1d,30.9d),(192.1d,30.9d),(192.1d,33.9d),(189.1d,33.9d)}';<br> \
&n \
bsp; &nbs \
p; \
&n \
bsp; &nbs \
p; \
QUERY PLAN &nbs \
p; \
&n \
bsp; &nbs \
p; \
\
<br>---------------------------------------------------------------------------- \
------------------------------------------------------------------------------------------------------------------<br> Aggregate \
(cost=732312.22..732312.23 rows=1 width=40) (actual time=0.023..0.023 rows=1 \
loops=1)<br> -> Bitmap Heap Scan on dr1 \
(cost=9377.77..719172.89 rows=404287 width=16) (actual time=0.016..0.016 rows=0 \
loops=1)<br> Recheck Cond: (pos <@ \
'{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , \
0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , \
0.591666616426078)}.<br>.'::spoly)<br> \
-> 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)<br> \
Index Cond: (pos <@ '{(3.30041761552128 , 0.539306738866248),(3.35277749308111 , \
0.539306738866248),(3.35277749308111 , 0.591666616426078),(3.30041761552128 , \
0.5916666164260.<br>.78)}'::spoly)<br> Planning time: 0.243 \
ms<br> Execution time: 0.177 ms<br>(7 rows)<o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>Any idea what can cause this \
?<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>Thank \
you<o:p></o:p></span></p><p style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'>Isabella<o:p></o:p></span></p><p \
style='background:white'><span \
style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div></div></div></body></html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic