[postgis-users] problems optimising PostGIS queries

[Gavin Fleming]

Postgres 8.0.1 on an up-to-date Gentoo system. 
POSTGIS="1.1.0CVS" GEOS="2.1.2" PROJ="Rel. 4.4.8, 3 May 2004" USE_STATS DBPROC="0.3.0" RELPROC="0.3.0"
 
I'm struggling to get anything but very simple PostGIS queries to work fast, if at all. I'm working both from the psql terminal and with Python via psycopg. What I really want to do is select fires that fall within a buffer of powerlines and then match those fire to 'towers'. So far I've been attempting this in one query and relying on PostGRES query planner to optimise it. I've tried forcing bounding box queries before doing distance predicates and so forth. My features are in latlong and buffer and distance operations need to be in metres so I also need to do some on-the-fly GeomUnions, transforms, buffers and expands.
 
Here is a simple query that does not even work predictably and as you can see doesn't even use the Gist indexes as it should. What seems to be concerning are the LWGEOM_gist_joinsel notice and the OM_gist_joinsel 'Examine'.
 
Any suggestions as to query approach or what these errors mean and what to do about them?
 
many thanks
 
Gavin

 
SET enable_seqscan TO off;
explain SELECT t.gid,t.objectid,t.id,t.sub_cat,t.lat,t.longx,f.fid,astext(f.the_geom) FROM towers t,fire f
WHERE (f.tick > '2005-07-18') AND (expand(f.the_geom,.01) && expand(t.the_geom,.01));
SET enable_seqscan TO on;

NOTICE:  LWGEOM_gist_joinsel called with arguments that are not column references

  gid  | objectid |  id   |   sub_cat   |     lat      |    longx    |  fid   |        astext
-------+----------+-------+-------------+--------------+-------------+--------+-----------------------
 47323 |    45547 | 47322 | 400kv_tower | -29.31961943 | 30.46373380 | 235516 | POINT(30.453 -29.339)
 ...skipping...
  76589 |    85420 | 76588 | 275kv_tower | -25.39751241 | 30.91836298 | 233293 | POINT(30.931 -25.415)
(4178 rows)
 
Examine: OM_gist_joinsel called with arguments that are not column references

 QUERY PLAN                            
-----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=334.95..7621023.93 rows=1386 width=90)
   Join Filter: (expand("inner".the_geom, 0.01::double precision) && expand("outer".the_geom, 0.01::double precision))
   ->  Seq Scan on towers t  (cost=0.00..2129.57 rows=76657 width=86)
   ->  Materialize  (cost=334.95..371.09 rows=3614 width=29)
         ->  Seq Scan on fire f  (cost=0.00..331.34 rows=3614 width=29)
               Filter: ((tick)::text > '2005-07-18'::text)
(6 rows)

espacio-# \d fire
                                     Table "public.fire"
   Column    |         Type          |                       Modifiers                      
-------------+-----------------------+-------------------------------------------------------
 fid         | integer               | not null default nextval('public.fire_fid_seq'::text)
 origin      | integer               | not null
 tick        | character varying(64) | not null
 temperature | real                  | not null
 confidence  | real                  |
 spread      | real                  |
 the_geom    | geometry              | not null
Indexes:
    "fire_pkey" PRIMARY KEY, btree (fid)
    "fire_ind_the_geom" gist (the_geom) CLUSTER
    "fire_ind_tick" btree (tick)
Check constraints:
    "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)

espacio-# \d towers
                                 Table "public.towers"
  Column  |       Type        |                        Modifiers
----------+-------------------+---------------------------------------------------------
 gid      | integer           | not null default nextval('public.towers_gid_seq'::text)
 objectid | bigint            |
 id       | character varying |
 sub_cat  | character varying |
 lat      | numeric           |
 longx    | numeric           |
 tenth    | bigint            |
 the_geom | geometry          | not null
Indexes:
    "towers_pkey" PRIMARY KEY, btree (gid)
    "tower_geom_index" gist (the_geom) CLUSTER
Check constraints:
    "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050722/d91277f0/attachment.html>