[mapserver-users] performance problem with PostGIS layer

[simon mercier]

Paul, Dave

... thank's for your help

After lots test.... I  find something....  By tracing on PostgreSQL, I find
that my mapserver execute this query:
SELECT  asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
from pclp_s
WHERE the_geom && setSRID('BOX3D(-71.593372 46.788388,-71.545372
46.836388)'::BOX3D,find_srid('','pclp_s','the_geom'))

THIS query don't use my GiST index?????
(EXPLAIN = Seq Scan on pclp_s  (cost=0.00..46282.50 rows=2 width=16))

But, if I try this one use it...
SELECT  asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
from pclp_s
WHERE the_geom && GeometryFromText('BOX3D(-71.593372 46.788388,-71.545372
46.836388)'::BOX3D,find_srid('','pclp_s','the_geom'))
(EXPLAIN = Index Scan using idx_geom_pclp_s on pclp_s  (cost=0.00..8.93
rows=2 width=16) )


And now... why setSRID function (the one use by MapServer) don't use my
GiST index and GeometryFromText use it?

I find that GeometryFromText use ISCACHABLE and setSRID dont'use it... It
is the problem?

Dave Blasby a écrit :

> simon mercier wrote:
> > --I have good result when retrieving data with spatial SQL query :
> > explain select count(*) from pclp_s where the_geom &&
> > GeometryFromText('BOX3D(-72.15 46.43, -72.13 46.45)'::BOX3D,-1)
> > Aggregate  (cost=8.94..8.94 rows=1 width=0)
> >   ->  Index Scan using idx_geom_pclp_s on pclp_s  (cost=0.00..8.93
> > rows=2 width=0)
>
> I dont know why you'd be having performance problems.  The query that
> mapserver sends to postgresql/PostGIS should take about the same amount
> of time as the one you have above.  How long does the above query
> actually take to perform (and how many rows are actually selected)?
>
> There was a minor problem with WKB and polygons a while ago - make sure
> you have the latest CVS version of PostGIS and Mapserver.
>
> Do the individual polygons have a *HUGE* number of points in them?
>
> Another strategy would be to create a new table ("pclp_s_small") with
> just the results of the above query in it.  Ie.
> CREATE TABLE pclp_s_small AS SELECT * FROM pclp_s WHERE the_geom && ...
> Then use this table with mapserver and see if you get better
> performance.
> If you do, then the performance problem has something to do with the
> GiST indexing on pclp_s.  If the performance still stinks, it has
> something to do with the communication between mapserver and
> postgresql/PostGIS.
>
> dave

--
Simon Mercier
Technicien en Géomatique
Direction des technologies de l'information
La Financière agricole du Quebec
5825, rue Saint-Georges
Lévis, Québec,Canada G6V 4L2
http://www.fadq.qc.ca/