Hi Micheal,<br>
Thank you for reply.<br>
I am not sure about whether "xxxxxx9999" represents some thing special
in data or not. I have indexes on geometry and id coloumns, i vacuumed
and analyzed the tables. Here are the EXPLAIN ANALYSE results of
slowqery and faster one:<br>
<span style="color: rgb(153, 51, 0);">slow query:</span><br>
<br>
<span style="font-weight: bold;">explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on c.stfid=<span style="color: rgb(255, 0, 0);">'220750501002999'</span> and d.the_geom&&c.the_geom and intersects(
d.the_geom,c.the_geom));</span><br style="font-weight: bold;">
<br style="font-weight: bold;">
<span style="font-weight: bold;">
QUERY PLAN</span><br style="font-weight: bold;">
<span style="font-weight: bold;">------------------------------------------------------------------------------------------------------------------------------------------------------</span><br style="font-weight: bold;">
<span style="font-weight: bold;"> Aggregate (cost=9.06..9.07 rows=1 width=0) (actual time=973892.916..973892.917 rows=1 loops=1)</span><br style="font-weight: bold;">
<span style="font-weight: bold;"> -> Nested Loop (cost=0.00..9.05 rows=1 width=0) (actual time=973892.901..973892.901 rows=0 loops=1)</span><br style="font-weight: bold;">
<span style="font-weight: bold;"> Join Filter: intersects("inner".the_geom, "outer".the_geom)</span><br style="font-weight: bold;">
<span style="font-weight: bold;">
-> Index Scan using stfidx_la_blocks on la_blocks c
(cost=0.00..3.03 rows=1 width=585) (actual time=0.035..0.046 rows=1
loops=1)</span><br style="font-weight: bold;">
<span style="font-weight: bold;">
Index Cond: ((stfid)::text = '220750501002999'::text)</span><br style="font-weight: bold;">
<span style="font-weight: bold;">
-> Index Scan using geomidx_la_damage on la_damage d
(cost=0.00..6.01 rows=1 width=21) (actual time=2018.848..44473.260
rows=1139 loops=1)</span><br style="font-weight: bold;">
<span style="font-weight: bold;">
Index Cond: (d.the_geom && "outer".the_geom)</span><br style="font-weight: bold;">
<span style="font-weight: bold;">
Filter: (d.the_geom && "outer".the_geom)</span><br style="font-weight: bold;">
<span style="font-weight: bold;"> Total runtime: <span style="color: rgb(255, 0, 0);">973918.391</span> ms</span><br style="font-weight: bold;">
<span style="font-weight: bold;">(9 rows)</span><br>
<br>
<span style="color: rgb(153, 0, 0);">Faster Query<br>
<br>
<span style="color: rgb(0, 0, 0); font-weight: bold;"> explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on c.stfid='<span style="color: rgb(255, 0, 0);">220750501002995</span>' and d.the_geom&&c.the_geom
and intersects(d.the_geom,c.the_geom));</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
QUERY PLAN</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">----------------------------------------------------------------------------------------------------------------------------------------------</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;"> Aggregate (cost=9.06..9.07 rows=1 width=0) (actual time=26.307..26.307 rows=1 loops=1)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;"> -> Nested Loop (cost=0.00..9.05 rows=1 width=0) (actual time=26.299..26.299 rows=0 loops=1)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;"> Join Filter: intersects("inner".the_geom, "outer".the_geom)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
-> Index Scan using stfidx_la_blocks on la_blocks c
(cost=0.00..3.03 rows=1 width=585) (actual time=26.183..26.187 rows=1
loops=1)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
Index Cond: ((stfid)::text = '220750501002995'::text)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
-> Index Scan using geomidx_la_damage on la_damage d
(cost=0.00..6.01 rows=1 width=21) (actual time=0.102..0.102 rows=0
loops=1)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
Index Cond: (d.the_geom && "outer".the_geom)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">
Filter: (d.the_geom && "outer".the_geom)</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;"> Total runtime: <span style="color: rgb(255, 0, 0);">26.386</span> ms</span><br style="color: rgb(0, 0, 0); font-weight: bold;">
<span style="color: rgb(0, 0, 0); font-weight: bold;">(9 rows)</span><br style="font-weight: bold;">
<br>
<span style="color: rgb(0, 0, 0);">Thanking you<br>
vamsee<br>
</span></span><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:00:29AM -0600, vamsee movva wrote:<br>> "select count(*) from locations l1,county l2 where l1.the_geom &&<br>> l2.the_geom and intersects(l2.the_geom,l1.the_geom) and l2.countyid=
some<br>> particular ID"<br>><br>> It gave some results, but i am not sure whether the query is correct or not,<br>> I have one more problem with this query, this query taking lot of time<br>> when some
<br>> particular ID ends with 9999(ex:220750501002999,xxxxxxx9999), do u have any<br>> idea about this strange situation.<br><br>Are you sure 9999 is relevant or might it just be coincidence? Does<br>9999 represent anything special in the data? Do you have indexes
<br>on the geometry and ID columns? Have you vacuumed and analyzed the<br>tables? What does EXPLAIN ANALYZE show for a slow query and for a<br>faster one?<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>