<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>2 things</p>
<p>First, unfortunately the full query doesn't work as in some cases
the subquery returns more than one row. Not sure why, as the geom
should be a rectangle (more or less given the projection, but was
created from 4 points), but maybe right near the boundary there
are issues (on reflection I guess the north/south edges are not
quite parallel).</p>
<p>Second - I forgot to complete part of my response. Should have
said <br>
</p>
<blockquote>Second, you lost me a little with your second
suggestion. As I gather _st_contains explicitly avoids the index,
so the explain still uses the sequential scan. I assume I'm
misunderstanding your suggestion.<br>
<br>
</blockquote>
Rgds<br>
<br>
Bill<br>
<div class="moz-cite-prefix">On 2/12/2016 5:29 PM, Bill Measday
wrote:<br>
</div>
<blockquote
cite="mid:66e5d6c6-0c2d-4935-8675-233dc806b069@measday.com"
type="cite">Thanks Regina,
<br>
<br>
First, your first suggestion didn't seem to achieve anything -
even the costs in the explain remained the same (1e10).
<br>
<br>
Second, you lost me a little with your second suggestion. As I
gather _st_contains explicitly avoids the index.
<br>
<br>
Tried
<br>
<br>
explain SELECT address_default_geocode_pid,
<br>
(SELECT elevation FROM m_elevations e WHERE e.geom &&
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)),
<br>
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
<br>
FROM address_default_geocode
<br>
WHERE latitude = -33.87718472 AND longitude = 151.27544336;
<br>
<br>
(ie changed WHERE ST_Contains(e.geom,
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)) to WHERE
e.geom && ST_SetSRID(ST_MakePoint(longitude, latitude),
4326))
<br>
<br>
and that seems to have done the trick. Tee explain now uses the
index and the actual query seems more or less on par with the
version run under 9.5.
<br>
<br>
Odd though, but thank you very much for the help/solution. Now
I'll try the full insert query.
<br>
<br>
Regards
<br>
<br>
Bill
<br>
<br>
<br>
On 2/12/2016 3:48 PM, Regina Obe wrote:
<br>
<blockquote type="cite">I have seen this on rare occasions in past
but not recently where the planner doesn't break apart the
ST_Contains or ST_Intersects into the component steps.
<br>
We made some changes to costing in 2.3.0, but ST_Contains (and
_ST_Contains) wasn't one of them so that doesn't quite explain
this.
<br>
Couple of things to try
<br>
<br>
1) On the _ST_Contains function in set the cost to something
higher like 1000:
<br>
<br>
CREATE OR REPLACE FUNCTION _st_contains(geom1 geometry, geom2
geometry) RETURNS boolean AS
<br>
'$libdir/postgis-2.3', 'contains'
<br>
LANGUAGE c IMMUTABLE STRICT
<br>
COST 1000;
<br>
<br>
And then try your query again/
<br>
<br>
<br>
2) If Item 1 doesn't work what happens if you explicitly put in
&& and _ST_Contains
<br>
<br>
That should force the index to kick in.
<br>
<br>
<br>
-----Original Message-----
<br>
From: postgis-users
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of Bill
Measday
<br>
Sent: Thursday, December 01, 2016 7:47 PM
<br>
To: <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
Subject: [postgis-users] Substantial different index use between
9.5 and 9.6 using Postgis
<br>
<br>
This is a post I put on the potgres performance list. They
suggested I take it up on the PostGis list
<br>
<br>
Postgres versions 9.5.4 and 9.6.0 running on Windows Server
2012.
<br>
Installed using EnterpriseDB. Both instances are on the same
server, postgresql.conf for both are the same except
max_locks_per_transaction =
<br>
200 in 9.6 (caused insertion errors otherwise).
<br>
<br>
On 9.5:
<br>
geo=# SELECT PostGIS_full_version();
<br>
postgis_full_version
<br>
<br>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4090"
PROJ="Rel. 4.9.1,
<br>
04 March 2015" GDAL="GDAL 2.0.2, released 2016/01/26"
LIBXML="2.7.8"
<br>
LIBJSON="0.12" RASTER
<br>
(1 row)
<br>
<br>
On 9.6:
<br>
<br>
postgis_full_version
<br>
<br>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 r4090"
PROJ="Rel. 4.9.1,
<br>
04 March 2015" GDAL="GDAL 2.1.1, released 2016/07/07"
LIBXML="2.7.8"
<br>
LIBJSON="0.12" RASTER
<br>
(1 row)
<br>
<br>
Databases on the 2 instances are the same (as far as I can
tell).
<br>
<br>
I have 2 relevant tables (created using same script in both
instances, except I added an auto increment RID to the 9.6
version as primary key to the larger of the 2 tables - other
already had one). One contains a geometry column (geom
geometry(1107464) - a polygon) with gist index.
<br>
This table has around 10 billion records. The disks these
databases on aren't particularly fast, and indexing took about a
week.
<br>
Second table has latitude (numeric(10, 8)), and longitude
(numeric(11,
<br>
8)) and about 10 million records.
<br>
<br>
The query I'm running is (a part of an insertion into a new
table I was trying to run)
<br>
SELECT address_default_geocode_pid,
<br>
(SELECT elevation FROM m_elevations e WHERE
ST_Contains(e.geom, ST_SetSRID(ST_MakePoint(longitude,
latitude), 4326))),
<br>
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
<br>
FROM address_default_geocode;
<br>
<br>
Under 9.5 the insertion takes about 11 hours. I gave up on 9.6.
<br>
<br>
I thought I'd try just one record, so:
<br>
<br>
SELECT address_default_geocode_pid,
<br>
(SELECT elevation FROM m_elevations e WHERE
ST_Contains(e.geom, ST_SetSRID(ST_MakePoint(longitude,
latitude), 4326))),
<br>
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
<br>
FROM address_default_geocode
<br>
WHERE latitude = -33.87718472 AND longitude =
151.27544336;
<br>
<br>
This returns 3 rows (which is more than the average I'd expect
BTW). On
<br>
9.5 takes a few seconds (3-5) and again I gave up on 9.6
<br>
<br>
Looking just at the query shown above, I note a difference in
explained behaviour. Here is the output from 9.5:
<br>
<br>
QUERY PLAN
<br>
<br>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
Seq Scan on address_default_geocode (cost=0.00..37760293.94
rows=1
<br>
width=25)
<br>
Filter: ((latitude = '-33.87718472'::numeric) AND
(longitude =
<br>
151.27544336))
<br>
SubPlan 1
<br>
-> Bitmap Heap Scan on m_elevations e
<br>
(cost=282802.21..37401439.43 rows=3512160 width=8)
<br>
Recheck Cond: (geom ~
<br>
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
<br>
precision, (address_default_geocode.latitude)::double
precision), 4326))
<br>
Filter: _st_contains(geom,
<br>
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
<br>
precision, (address_default_geocode.latitude)::double
precision), 4326))
<br>
-> Bitmap Index Scan on m_elevations_geom_idx
<br>
(cost=0.00..281924.17 rows=10536480 width=0)
<br>
Index Cond: (geom ~
<br>
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
<br>
precision, (address_default_geocode.latitude)::double
precision), 4326))
<br>
(8 rows)
<br>
<br>
From 9.6
<br>
<br>
QUERY PLAN
<br>
<br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<br>
Seq Scan on address_default_geocode
<br>
(cost=10000000000.00..23297309357.08 rows=1 width=49)
<br>
Filter: ((latitude = '-33.87718472'::numeric) AND
(longitude =
<br>
151.27544336))
<br>
SubPlan 1
<br>
-> Seq Scan on m_elevations e
<br>
(cost=10000000000.00..13296950520.12 rows=3512159563 width=8)
<br>
Filter: st_contains(geom,
<br>
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
<br>
precision, (address_default_geocode.latitude)::double
precision), 4326))
<br>
(5 rows)
<br>
<br>
Interestingly (change is hard coding of coordinates in second
line):
<br>
<br>
explain SELECT address_default_geocode_pid,
<br>
(SELECT elevation FROM m_elevations e WHERE
ST_Contains(e.geom,
<br>
ST_SetSRID(ST_MakePoint(151.27544336, -33.87718472), 4326))),
<br>
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
<br>
FROM address_default_geocode
<br>
WHERE latitude = -33.87718472 AND longitude =
151.27544336;
<br>
<br>
Gives (in 9.6)
<br>
<br>
QUERY PLAN
<br>
<br>
--------------------------------------------------------------------------------------------------------
<br>
Seq Scan on address_default_geocode
<br>
(cost=10037428497.36..10037787334.33 rows=1 width=49)
<br>
Filter: ((latitude = '-33.87718472'::numeric) AND
(longitude =
<br>
151.27544336))
<br>
InitPlan 1 (returns $0)
<br>
-> Bitmap Heap Scan on m_elevations e
<br>
(cost=272194.20..37428497.36 rows=3512160 width=8)
<br>
Recheck Cond: (geom ~
<br>
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
<br>
Filter: _st_contains(geom,
<br>
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
<br>
-> Bitmap Index Scan on m_elevations_geom_idx
<br>
(cost=0.00..271316.16 rows=10536480 width=0)
<br>
Index Cond: (geom ~
<br>
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
<br>
(8 rows)
<br>
<br>
Which looks better.
<br>
<br>
So for some reason, 9.6 planner decides not to use the index for
a small
<br>
number of records returned from address_default_geocode.
<br>
I have vacuum analysed both tables.
<br>
Clearly a sequential scan on 10 billion records is pretty slow
(to say
<br>
the least).
<br>
<br>
Has anyone seen anything like this/got any thoughts?
<br>
<br>
I tried "set enable_seqscan=false" but didn't seem to have any
effect.
<br>
<br>
Regards
<br>
<br>
Bill
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a>
<br>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
<br>
</body>
</html>