[postgis-users] Not able to excute spatial query on postgresql database with postgis support

[vamsee movva]

Hi Micheal,
                     Thank you for reply.
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:
slow query:

explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on
c.stfid='220750501002999' and d.the_geom&&c.the_geom and intersects(
d.the_geom,c.the_geom));

                                                                      QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=9.06..9.07 rows=1 width=0) (actual time=
973892.916..973892.917 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..9.05 rows=1 width=0) (actual time=
973892.901..973892.901 rows=0 loops=1)
         Join Filter: intersects("inner".the_geom, "outer".the_geom)
         ->  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)
               Index Cond: ((stfid)::text = '220750501002999'::text)
         ->  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)
               Index Cond: (d.the_geom && "outer".the_geom)
               Filter: (d.the_geom && "outer".the_geom)
 Total runtime: 973918.391 ms
(9 rows)

Faster Query

 explain ANALYZE (select count(*) from la_blocks as c join la_damage as d on
c.stfid='220750501002995' and d.the_geom&&c.the_geom and intersects(
d.the_geom,c.the_geom));
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=9.06..9.07 rows=1 width=0) (actual
time=26.307..26.307rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..9.05 rows=1 width=0) (actual time=
26.299..26.299 rows=0 loops=1)
         Join Filter: intersects("inner".the_geom, "outer".the_geom)
         ->  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)
               Index Cond: ((stfid)::text = '220750501002995'::text)
         ->  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)
               Index Cond: (d.the_geom && "outer".the_geom)
               Filter: (d.the_geom && "outer".the_geom)
 Total runtime: 26.386 ms
(9 rows)

Thanking you
vamsee


On 10/30/06, Michael Fuhr <mike at fuhr.org> wrote:
>
> On Mon, Oct 30, 2006 at 09:00:29AM -0600, vamsee movva wrote:
> > "select count(*) from locations l1,county l2 where l1.the_geom &&
> > l2.the_geom and intersects(l2.the_geom,l1.the_geom) and l2.countyid=some
> > particular ID"
> >
> > It gave some results, but i am not sure whether the query is correct or
> not,
> > I have one more problem with this query, this query taking lot of time
> > when some
> > particular ID ends with 9999(ex:220750501002999,xxxxxxx9999), do u have
> any
> > idea about this strange situation.
>
> Are you sure 9999 is relevant or might it just be coincidence?  Does
> 9999 represent anything special in the data?  Do you have indexes
> on the geometry and ID columns?  Have you vacuumed and analyzed the
> tables?  What does EXPLAIN ANALYZE show for a slow query and for a
> faster one?
>
> --
> Michael Fuhr
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061030/9e162568/attachment.html>