Update 2 (below) suggests 14% of the table geometries are stored out-of-line with the rest of the table records. This may be a useful clue.
The Problem:
We've got a table with appx 170,000 WGS84 polygons, a GIST geometry index, and we're wanting to get a selection set ordered by descending feature area values. Since this data was "born" in ArcSDE, it has a shape_area
field.
Querying with ORDER BY shape_area DESC
and LIMIT 2000
predicates, the intersection--using &&
--took appx 25000 milliseconds (25 sec) to finish.
So ..perhaps Clustering by descending polygon area?
Looking for more speed, I tried clustering the table by a shape_area
index like this:
CREATE INDEX shape_area_idx
ON layer_wgs84
USING btree
(shape_area DESC NULLS LAST)
WITH (FILLFACTOR=100) ;
ALTER TABLE layer_wgs84 CLUSTER ON shape_area_idx ;
cluster layer_wgs84 ;
-- then I dropped and recreated the geometry index
VACUUM ANALYZE layer_wgs84 ;
Afterward I could run the intersection without the expensive ORDER BY
clause, but it still took over 3000 ms (3 sec) to complete.
..FWIW I've also tried using a WITH Query (a.k.a. Common Table Expression), to ensure the BBox intersection is processed first. But this doesn't really help at low zoom if the BBox covers the whole 170,000-feature table. Even with the LIMIT
, it still takes 3-4 seconds.
I'd like to get this down to about 500 ms.
[ACTUAL QUESTION]
Strategically, is there a better approach I may not be aware of to perform a spatial intersection ordered by their shape area descending?
[UPDATE 1]
When I omit the geometry from the query, it becomes stupid-fast. 50ms! So I'm thinking my query is as good as it's going to get, and the bottle-neck must be the instance hitting disk to fetch the geometry objects.
[UPDATE 2]
Because pulling the geometries was killing the return speed, I started wondering if the geometry objects were stored inline or out-of-line with the rest of the record fields, and consequently, whether I might consider storing all the geometries in a different field as VARCHAR(N)
or TEXT
in an effort to force them inline and avoid any expensive disk hits. Googling that question led me to this SO thread (how to check the storage mode for each table column) and the docs page discussing TOAST(The Oversized-Attribute Storage Technique).
So I checked this table (on my dev system) and the geometry column is setup for "MAIN", which means:
MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough to fit on a page.)
And this passage suggests the implication for geometry values under default conditions:
The TOAST code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB) or no more gains can be had.
Sooooooo then I wondered.. What percent of our table is stored out-of-line??
ANSWER: appx 14% (Including a military base parcel that probably gets pulled by a shocking number of queries.) For the curious, here's I polled the DB to learn this..
with
total as (
select count(*) from parcels_cama_wgs84 as count),
out_of_line as (
select count(*) from parcels_cama_wgs84 as count
where ST_mem_size(the_geom)*0.001 > 2)
select
total.count,
out_of_line.count,
(out_of_line.count::DECIMAL/total.count::DECIMAL)::DECIMAL as perc
from total, out_of_line;
| total | outofline | percent
| 164861 | 23193 | 0.14068
Which led me to ask.. What if I modify the query to return ONLY the UNtoasted geometries??
To do this I modified the WHERE
clause of my inner select (see query below) to include this..
AND ST_mem_size(the_geom)*0.001 < 2 -- only geoms less than 2kb
After that my test-case query went from 1400 ms to 850 ms..
However. While this is an interesting discovery it doesn't lend any solution, plus it's not an option for us to omit all > 2kb polygons from these layer queries. So while this does tell me something, I'm still looking for the Aha!-moment..
I can force the geometries inline by changing the column storage mode to PLAIN
, which I'll try and remark on..
[Obligatory Query and Explain Plan | tl;dr;]
This is my current query and its explain plan. However I want to stress I'm really just asking if there is an alternative approach/tactic I should be considering.
SELECT
l.gid,
st_astext(l.the_geom) AS geom,
l.tms AS tms,
l.shape_area_int
FROM (
SELECT * FROM
parcels_cama_wgs84
WHERE
the_geom && ST_Envelope(ST_GeogFromText('SRID=4326;POLYGON((-81.08030319213867 34.060588119230346, -81.08030319213867 34.09318398156763, -81.02404117584229 34.09318398156763, -81.02404117584229 34.060588119230346, -81.08030319213867 34.060588119230346))')::geometry)
) AS l
ORDER BY shape_area_int DESC
LIMIT 2000;
Explain Plan:
"Limit (cost=9970.86..9975.86 rows=2000 width=1155) (actual time=484.172..489.691 rows=2000 loops=1)"
" -> Sort (cost=9970.86..9977.31 rows=2580 width=1155) (actual time=484.169..489.564 rows=2000 loops=1)"
" Sort Key: parcels_cama_wgs84.shape_area_int"
" Sort Method: external merge Disk: 6640kB"
" -> Bitmap Heap Scan on parcels_cama_wgs84 (cost=136.32..8519.16 rows=2580 width=1155) (actual time=2.357..463.939 rows=2569 loops=1)"
" Recheck Cond: (the_geom && '0103000020E61000000100000005000000000000B0234554C06C4FFB59C1074140000000B0234554C009ACE473ED0B4140000000E4894154C009ACE473ED0B4140000000E4894154C06C4FFB59C1074140000000B0234554C06C4FFB59C1074140'::geometry)"
" -> Bitmap Index Scan on parcels_cama_wgs84_the_geom_geom_idx (cost=0.00..135.67 rows=2580 width=0) (actual time=2.066..2.066 rows=2569 loops=1)"
" Index Cond: (the_geom && '0103000020E61000000100000005000000000000B0234554C06C4FFB59C1074140000000B0234554C009ACE473ED0B4140000000E4894154C009ACE473ED0B4140000000E4894154C06C4FFB59C1074140000000B0234554C06C4FFB59C1074140'::geometry)"
"Total runtime: 491.461 ms"
WORK_MEM
, probably adjust the shared buffers too.