[fdo-users] postgis and performance

[Mateusz Loskot]

alucas wrote:
> Hello Mateusz,
> 
> In order to understand the problem, you need to read the entire post. In
> summary, I'm testing the fdo provider with mapguide enterprise 2009 and I've
> problems about the performance for reading large volumes of data (i.e layer
> with aprox 500.000 records). Zac, told me that the problem could be in the
> string matching that the provider need to do for column name in the "select
> *" query. 

I've tried but couldn't identified anything like that.
Best would be to profile provider to find out bottlenecks.

> I've a question for you, if I create a spatial index in postgis (gist
> index), the provider uses it?

Yes, it does by applying suggestions described in the PostGIS manual:

http://www.postgis.org/documentation/manual-1.3/ch04.html#id2546880

See it here:

http://trac.osgeo.org/fdo/browser/trunk/Providers/PostGIS/Src/Provider/FilterProcessor.cpp?rev=3904#L354

> Another problems occurs when I've a database with aprox 240 tables. From
> Autocad Map 2009, the operation for reading the schemas is very expensive in
> time. It takes aprox. 5 minutes for reading the schemas in this case, is
> normal?

I've not used the PostGIS provider with such big number of tables, so
have no experience.

What I would suggest is to try a simple profiling of DescribeSchemaCommand:

1. Open local copy of DescribeSchemaCommand.cpp

http://trac.osgeo.org/fdo/browser/trunk/Providers/PostGIS/Src/Provider/DescribeSchemaCommand.cpp

2. Go to line 145

3. Wrap it with FDOLOG_* macros as follows:

FDOLOG_PROFILE_BLOCK;

FdoPtr<FdoFeatureSchemaCollection> logicalSchema =
mConn->GetLogicalSchema();

FDOLOG_PROFILE_CHECKPOINT

4. Confirm that FDLOG_DEBUG is uncommented and set

http://trac.osgeo.org/fdo/browser/trunk/Providers/PostGIS/Src/Provider/Logger.h#L52

5. Rebuild provider

6. In your filesystem scan for file named XXX_fdopostgis.log, where XXX
is name of process/executable that loaded the provider (MapGuide or Map
3D, etc.)

7. Scan for marker "DescribeSchemaCommand::+Execute" and you will get
some timing of the DescribeSchemaCommand execution.

At least, it should tell if this command is a bottleneck or not.


Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
Charter Member of OSGeo, http://osgeo.org