[postgis-users] Postgis performance

[Julio Galindo]

Hi Ibrahim and Webb,



I will like to summarize some points that in my case, take me a time to
understand how to improve the Mapserver - Postgis interaction:



You need a table with an index, in the old versions of Postgres there where
by default created with OIDS, the latest version doesn't do this:



CREATE TABLE test.geotable(

    id_geom serial -- this is necesary for Mapserver

);

SELECT AddGeometryColumn('test','geotable','poly',32719,2);



(In the following example I am using and SRID 32719)



You must create a GIST index if you really want to improve the postgis (&&,
..) functions, (see point 4.7.2.3 on documentation*)*:



CREATE INDEX gistid_poly_test ON test.geotable USING GIST(poly
GIST_GEOMETRY_OPS);



If you have a large amount of geometries (poly in our example), it is a good
idea to cluster them using the GIST index. This is something that improve
the disk access time. Please keep in mind that after you change the geometry
information, you need to redo the cluster. A good recommendation is to do
this task every day on changing tables:



At the creation time of the table I recommend to include the
following instruction:



ALTER TABLE test.geotable CLUSTER ON gist_poly_test;



Then, on the daily work only you need to instruct:



SELECT CLUSTER test.geotable;



Within the PHP command I strongly recommend to use the index, (point
4.7.2.4of the documentation) as well as the SRID (
4.7.3) in the Mapserver command:



DATA "poly FROM test.geotable USING UNIQUE id_geom USING SRID=32719"



You must use capital letters for 'USING UNIQUE' and 'USING SRID' (otherwise
the parser for Mapserver will not understand)



As the Postgis documentation mention, this is something optional. But, keep
in mind that if you instruct Mapserver in the following way:



DATA "poly FROM test.geotable"



It will also work but making two additional SELECT instructions to draw:
(one to get the version of postgis, second to know the id_geom index, and
finally within the select poly it will use a function to detect the SRID of
the poly - in our example 32719).



If someone have other suggestions, I will appreciate comments, to improve
the speed of Mapserver.



Regards,



Julio

On 4/16/07, TECHER David <davidtecher at yahoo.fr> wrote:

> Hi Ibrahim
>
> Please take a look at
> http://postgis.refractions.net/docs/ch04.html#id2709413 questions
> 4.7.2.3 and 4.7.2.4
>
> I hope these helps!
>
> Ibrahim Khachab a écrit :
> > Hi to you all,
> >
> > I'm new with geographic data and I'm trying to set a server with
> > mapserver/posgis.
> > It is a dual Xeon 2.8Ghz cpus machine with 2Gbyte of Ram.
> > I have some data in SHP files that I converted to postgis data with
> > shp2pgsql.
> > I made some test with some map file and I noticed a strange thing:
> > The same maps  with the shp file are 10 times faster than those with
> > the Postgis data.
> > What can be the problem.
> > How I may debug about this?
> > I'm trying to read a lot these days about Postgresql perfoemance but
> > till now I found nothing that could change this situation.
> >
> >
> > Thanks
> >
> > Ibrahim
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
>
>
>
>
> ___________________________________________________________________________
> Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son
> interface révolutionnaire.
> http://fr.mail.yahoo.com
> _______________________________________________
> 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/20070416/054cf730/attachment.html>