Skip to content

Super Double-Box Search-Off


“A quick estimate of performance of PostGIS 2 with
geometry bounding boxes as doubles vs as floats”

All tests performed on Linux 2.6.38 x86_64
PostgreSQL 9.0.5
shared_buffers = 2400MB
temp_buffers = 64MB
work_mem = 512MB
maintenance_work_mem = 200MB

The Test – TIGER 2010 California

TIGER provides many possibilities for tests.. The one I chose here
uses PLACE (a large’ish MULTIPOLY) and EDGE (MULTILINE)

A random sampling of 100 EDGES in California shows the following characteristics:

select st_npoints(the_geom) from ca_edges order by random() limit 100;
min,median,mean,max stdev
2, 5.15, 16.333, 212 34.23

sorted, the first dozen entries are 2, the last are the following
[ … 36,37,43,44,51,57,58,68,68,79,206,206,212]

I reasoned that although many EDGES are trivially short, there
exist substantial numbers of non-trivial EDGES, so they would be ok for a test.

Next, pick some large PLACE geometries..
From the Dozen largest (by area) PLACE rows, I picked 4 well-known cities

now, the test (with indexes in place I trust)

select count(*) from ca_edges where st_intersects( the_geom,
(select st_envelope(the_geom) from ca_place where name = 'City_Name') );




preliminary conclusions

the search times were almost the same for each of the 4 cases
and I did not detect change with multiple runs of the same query
(though I could have tested that more, too)

search times were fast enough such that I believe indexes are being used
(I did not check all indexes carefully, but the database setup was the same
for both test contexts, so if they missed something, both tests likely missed
in the same way)

the result counts for both trunk and dblbox match, as a sanity check

performance hit is around 4% in each case


caveat: I could have misjudged and chosen a poor test case for some
obscure-to-me reason.. hopefully, this is useful and informative