[Benchmarking] Re: Remarks about Postgis

[Paul Ramsey]

On Mon, Aug 9, 2010 at 6:50 AM, Cédric Briançon
<cedric.briancon at geomatys.fr> wrote:
> Hi all,
>
> about the postgis database I have to remarks to discuss:
>
> 1) Switch to lower case: some columns in the database are now in lower case.
> In the shapefile like motorway.shp, you have the property "TIPO_0601" which
> is exactly what I wrote in my SLD files. In the postgis database you have
> the column "tipo_0601" so my styles will not work for both shapefiles and
> postgis.
> It is not really a big deal, I can duplicate my whole styles and change the
> case, but I want others to be aware that they have to change the case of
> their property name in their styles to use them on the postgis database, if
> they want to use it.

I can reload again forcing the cases up, if necessary. Depends on what
others want.

> 2) When launching a postgis "envelope" request (select envelope(the_geom)
> from contour_0), the response time is quite long around 20s directly on the
> db server. It is understandable because there are lots of data, but a WMS
> server in its GetCapabilities response is supposed to display the envelope
> for each defined layer. That requieres a significant amount of time to get
> the response (around 2minutes for me).
> I see that there is already an index on the "the_geom" columns, which is a
> good thing. Can something be done to improve this waiting time

The extent of a table is not something an index can help with, you
have to scan every record. I'm surprised you haven't run across this
before, it's a common issue with big databases. MapServer "solves" it
by letting you hand-add an extent in the configuration metadata.
GeoServer similarly stores the extent in metadata, scanning the table
only once at configuration time.

For desktop clients that haven't that luxury, we have added utility
functions that provide an estimated extent quickly (rather than an
exact extent slowly). Eg:

select st_envelope(st_estimated_extent('nyc_buildings','the_geom'));

The estimates tend to be underdetermined because they are based on
sampling the table, so it would be wise to expand by them about 25%
for safety if you want to be sure to take in every feature.

Best,

P

?
>
> Thanks,
> Cédric
>
>
>