[postgis-users] Spatial Join Performance

Eric Shuman erics at ameri-title.com
Fri Nov 17 16:28:51 PST 2006

Paul, Brent, etc...

So I have been trying the various work arounds for the TOAST issue, but
can't seem to force the query to use the spatial index on the table in
question.  None of the work arounds seem to change anything.  Below are some
of my stats and the query with query plan.

SELECT version();
"PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.5-13)"

SELECT postgis_version();

SET enable_seqscan=OFF;
SET random_page_cost=1;
SET effective_cache_size=1000; -- Have tried up to 10000000 as well


SELECT taxlot,zone_
FROM  taxlot,zoning
  taxlot.the_geom && zoning.the_geom

---------  Query Plan --------------------------
"Nested Loop  (cost=100000000.00..100003612.20 rows=62135 width=23)"
"  Join Filter: contains("outer".the_geom, "inner".the_point_geom)"
"  ->  Seq Scan on zoning  (cost=100000000.00..100000132.49 rows=1149
"  ->  Index Scan using taxlot_geom_index on taxlot  (cost=0.00..3.01 rows=1
"        Index Cond: (taxlot.the_geom && "outer".the_geom)"

Some of the things I have also tried:
-- SELECT AddGeometryColumn('','taxlot','the_point_geom','-1','POINT',2);
-- update taxlot set the_point_geom = centroid(taxlot.the_geom);
-- create index point_index on taxlot USING GIST (the_point_geom);
-- vacuum analyze taxlot;
-- vacuum analyze zoning;

-- SELECT addGeometryColumn('','zoning','bbox','-1','GEOMETRY','2');
-- UPDATE zoning set bbox = Envelope(Force_2d(the_geom));

Would the fact that I don't have a srid set on the tables matter?

Thanks again for all the help!

Eric Shuman

More information about the postgis-users mailing list