Query looks perfectly look to me. (You might use = instead of LIKE, but
that won't change your performance in this case.)
Your statistics look odd, you might run "ANALYZE" and then use EXPLAIN
ANALYZE to generate a query plan with both estimated and real time
measurements. But regardless, the plan you got here doesn't look too bad.
Looks like you have hit exactly the same "testing many small things
within one large thing" performance issue that everyone seems to be
having these last couple months.
http://geotips.blogspot.com/2007/06/performance-and-contains.html
Our first approximation at a performance boost for that is just getting
into internal test land now, so hopefully we'll have something suitable
for bleeding edge users in a month or so.
P.
Stephen Crawford wrote:
I'm trying to write an efficient spatial query between two table that in
effect says "give me all the features from table 1 that intsersect the
selected features from table 2". The following query works but takes about
50 seconds. Both table have a gist index. The "blm_lands" table is fairly
high-resolution, and the selection for "ANCHORAGE" includes many islands.
Is the query below the best way to do it? I have also included the query
plan.
Thanks,
Steve
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
SELECT DISTINCT(m.mlra_name) AS name, m.mlra_reg_s AS mlra_id FROM mlra AS
m, blm_lands_best AS o
WHERE m.the_geom && o.the_geom
AND intersects(m.the_geom,o.the_geom)
AND o.district LIKE 'ANCHORAGE';
QUERY PLAN
----------------------------------------------------------------------------
----------------------
Unique (cost=17.21..17.22 rows=1 width=26)
-> Sort (cost=17.21..17.21 rows=1 width=26)
Sort Key: m.mlra_name, m.mlra_reg_s
-> Nested Loop (cost=0.00..17.20 rows=1 width=26)
Join Filter: intersects("inner".the_geom, "outer".the_geom)
-> Seq Scan on blm_lands_best o (cost=0.00..7.61 rows=2
width=77)
Filter: ((district)::text ~~ 'ANCHORAGE'::text)
-> Index Scan using mlra_gist_index on mlra m
(cost=0.00..4.78 rows=1 width=114)
Index Cond: (m.the_geom && "outer".the_geom)
Filter: (m.the_geom && "outer".the_geom)
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Paul Ramsey
Refractions Research
http://www.refractions.net
[EMAIL PROTECTED]
Phone: 250-383-3022
Cell: 250-885-0632
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
