Substantial different index use between 9.5 and 9.6
Postgres versions 9.5 and 9.6 running on Windows Server 2012. Installed
using EnterpriseDB. Both instances are on the same server,
postgresql.conf for both are the same except max_locks_per_transaction =
200 in 9.6 (caused insertion errors otherwise). On 9.6, Postgis is
2.3.0, and I think same on 9.5 but not sure how to tell.
Databases on the 2 instances are the same (as far as I can tell).
I have 2 relevant tables (created using same script in both instances).
One contains a geometry column (geom geometry(1107464) - a polygon)
with gist index. This table has around 10 billion records. The disks
these databases on aren't particularly fast, and indexing took about a week.
Second table has latitude (numeric(10, 8)), and longitude (numeric(11,
8)) and about 10 million records.
The query I'm running is (a part of an insertion into a new table I was
trying to run)
SELECT address_default_geocode_pid,
(SELECT elevation FROM m_elevations e WHERE ST_Contains(e.geom,
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326))),
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
FROM address_default_geocode;
Under 9.5 the insertion takes about 11 hours. I gave up on 9.6.
I thought I'd try just one record, so:
SELECT address_default_geocode_pid,
(SELECT elevation FROM m_elevations e WHERE ST_Contains(e.geom,
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326))),
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
FROM address_default_geocode
WHERE latitude = -33.87718472 AND longitude = 151.27544336;
This returns 3 rows (which is more than the average I'd expect BTW). On
9.5 takes a few seconds (3-5) and again I gave up on 9.6
Looking just at the query shown above, I noted a difference in explained
behaviour. Here is the output from 9.5:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on address_default_geocode (cost=0.00..37760293.94 rows=1
width=25)
Filter: ((latitude = '-33.87718472'::numeric) AND (longitude =
151.27544336))
SubPlan 1
-> Bitmap Heap Scan on m_elevations e
(cost=282802.21..37401439.43 rows=3512160 width=8)
Recheck Cond: (geom ~
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
precision, (address_default_geocode.latitude)::double precision), 4326))
Filter: _st_contains(geom,
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
precision, (address_default_geocode.latitude)::double precision), 4326))
-> Bitmap Index Scan on m_elevations_geom_idx
(cost=0.00..281924.17 rows=10536480 width=0)
Index Cond: (geom ~
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
precision, (address_default_geocode.latitude)::double precision), 4326))
(8 rows)
From 9.6
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on address_default_geocode
(cost=10000000000.00..23297309357.08 rows=1 width=49)
Filter: ((latitude = '-33.87718472'::numeric) AND (longitude =
151.27544336))
SubPlan 1
-> Seq Scan on m_elevations e
(cost=10000000000.00..13296950520.12 rows=3512159563 width=8)
Filter: st_contains(geom,
st_setsrid(st_makepoint((address_default_geocode.longitude)::double
precision, (address_default_geocode.latitude)::double precision), 4326))
(5 rows)
Interestingly (change is hard coding of coordinates in second line):
explain SELECT address_default_geocode_pid,
(SELECT elevation FROM m_elevations e WHERE ST_Contains(e.geom,
ST_SetSRID(ST_MakePoint(151.27544336, -33.87718472), 4326))),
ST_SetSRID(ST_MakePoint(latitude, longitude), 4283)
FROM address_default_geocode
WHERE latitude = -33.87718472 AND longitude = 151.27544336;
Gives (in 9.6)
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on address_default_geocode
(cost=10037428497.36..10037787334.33 rows=1 width=49)
Filter: ((latitude = '-33.87718472'::numeric) AND (longitude =
151.27544336))
InitPlan 1 (returns $0)
-> Bitmap Heap Scan on m_elevations e
(cost=272194.20..37428497.36 rows=3512160 width=8)
Recheck Cond: (geom ~
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
Filter: _st_contains(geom,
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
-> Bitmap Index Scan on m_elevations_geom_idx
(cost=0.00..271316.16 rows=10536480 width=0)
Index Cond: (geom ~
'0101000020E610000036E3976ED0E86240B879C29647F040C0'::geometry)
(8 rows)
Which looks better.
So for some reason, 9.6 planner decides not to use the index for a small
number of records returned from address_default_geocode.
I have vacuum analysed both tables.
Clearly a sequential scan on 10 billion records is pretty slow (to say
the least).
Has anyone seen anything like this/got any thoughts?
I tried "set enable_seqscan=false" but didn't seem to have any effect.
Regards
Bill
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
