Hello Michael,<br>
Thank you for reply. I never spotted that particular query scanning
1139 rows, while others not.<br>
Here i am furnishing the results of the questions you asked:<br>
<span style="font-weight: bold;">How big is that index?<br>
</span>I am sorry, i didn't under stand your question.(excuse me i am poor in english).<br>
<span style="font-weight: bold;">How many rows are in la_damage?<br>
<span style="font-weight: bold;"><span style="font-weight: bold;"></span></span></span> select count(*) from la_damage;<br>
count<br>
-------<br>
85017<br>
(1 row)<br>
<span style="font-weight: bold;">What can you tell us about the geometries in la_damage ?<br>
</span>Honestly i don't have any idea about geometries and how to find geometries.<br>
<span style="font-weight: bold;">the geometry in la_blocks for stfid = 220750501002999? <br>
</span>I don't know how to see the geometry of the_geom column.<br>
<span style="font-weight: bold;">What's the output of the following query?</span><br style="font-weight: bold;">
<span style="font-weight: bold;">SELECT summary(the_geom) FROM la_blocks WHERE stfid = '220750501002999';<br>
<span style="font-weight: bold;"></span></span>please find the attached file "stfid.txt"<br>
<span style="font-weight: bold;">Here i tried same query for one more stfid <br>
</span>SELECT summary(the_geom) FROM la_blocks WHERE stfid = '220750501002995';<br>
summary<br>
---------<br>
<br>
MultiPolygon[B] with 1 elements<br>
Polygon[] with 2 rings<br>
ring 0 has 52 points<br>
ring 1 has 11 points<br>
<br>
(1 row)<br>
<span style="font-weight: bold;">Is the query significantly faster with distance() = 0 instead of intersects()? <br>
<span style="font-weight: bold;"></span></span>From my observation i think the query is significantly faster with distance()=0 and returned more results than intersects().<br>
<span style="font-weight: bold;">What version of PostgreSQL are you running? <br>
</span>PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu<br>
<br>
I hope i gave you enough information, to help me.<br>
Excuse me if i did any mistake in answering question.<br>
Thanking you<br>
vamsee movva<br>
<span style="font-weight: bold;"></span><span style="font-weight: bold;"><span style="font-weight: bold;"></span><br>
</span><br>
<br>
<br>
<span style="font-weight: bold;"><br style="font-weight: bold;">
</span><br style="font-weight: bold;">
<span style="font-weight: bold;"><br>
</span><br>
<span style="font-weight: bold;"><span style="font-weight: bold;"></span><br>
</span><br>
<br>
<br>
<br><br><div><span class="gmail_quote">On 10/30/06, <b class="gmail_sendername">Michael Fuhr</b> <<a href="mailto:mike@fuhr.org">mike@fuhr.org</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Mon, Oct 30, 2006 at 09:33:34AM -0600, vamsee movva wrote:<br>> explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on<br>> c.stfid='220750501002999' and d.the_geom&&c.the_geom and intersects(
<br>> d.the_geom,c.the_geom));<br>><br>> QUERY<br>> PLAN<br>> ------------------------------------------------------------------------------------------------------------------------------------------------------
<br>> Aggregate (cost=9.06..9.07 rows=1 width=0) (actual time=<br>> 973892.916..973892.917 rows=1 loops=1)<br>> -> Nested Loop (cost=0.00..9.05 rows=1 width=0) (actual time=<br>> 973892.901..973892.901
rows=0 loops=1)<br>> Join Filter: intersects("inner".the_geom, "outer".the_geom)<br>>
-> Index Scan using stfidx_la_blocks on la_blocks
c (cost=<br>> 0.00..3.03 rows=1 width=585) (actual time=0.035..0.046 rows=1 loops=1)<br>>
Index Cond: ((stfid)::text = '220750501002999'::text)<br>>
-> Index Scan using geomidx_la_damage on la_damage
d (cost=<br>> 0.00..6.01 rows=1 width=21) (actual time=2018.848..44473.260 rows=1139<br>> loops=1)<br>>
Index Cond: (d.the_geom && "outer".the_geom)<br>>
Filter: (d.the_geom && "outer".the_geom)<br>> Total runtime: 973918.391 ms<br><br>The index scan on geomidx_la_damage is taking a long time to find<br>1139 rows and then intersects() is taking forever to eliminate them
<br>all. How big is that index? How many rows are in la_damage? What<br>can you tell us about the geometries in la_damage and the geometry<br>in la_blocks for stfid = 220750501002999? What's the output of the<br>following query?
<br><br>SELECT summary(the_geom)<br>FROM la_blocks<br>WHERE stfid = '220750501002999';<br><br>Is the query significantly faster with distance() = 0 instead of<br>intersects()? What version of PostgreSQL are you running? What
<br>OS and hardware? What non-default settings do you have in<br>postgresql.conf?<br><br>--<br>Michael Fuhr<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote></div><br>