[postgis-users] SQLite and postGIS

[Frank Warmerdam]

Martin Chapman wrote:
> Rich,
> 
> One thing you should note about OGR performance when accessing certain
> databases like MySQL, PostgreSQL and SQLite is that OGR uses a strategy for
> these drivers that gets progressively worse for large datasets.  Query
> execution is always pretty quick in any database, it's accessing the rows
> quickly that is the true test of database performance in my opinion.  
> 
> In OGR, the SQLite driver becomes progressively slower over time when
> iterating over a large resultset from 0 to N.  Note that doesn't mean that
> SQLite is slow though, it's actually a really fast database if used
> optimally.  
> 
> For instance:
> 
> For each record that is requested after a query is executed, OGR starts at
> the beginning of the resultset (row zero) and searches forward until it
> finds the matching FID.  This pattern is repeated for every request.  This
> will execute very quickly for the first few thousand FID's or so but the
> further you iterate through the resultset, and the higher the index of each
> FID becomes, the slower each record request will become.  This is obviously
> a strategy of diminishing returns over any resultset of any size.  The
> solution to this problem is to use random access.  See my other postings to
> see how to make this change to OGR for MySQL, PostgreSQL and SQLite.   

Folks,

I would like to stress this behavior is only the case when attempting
to fetch features by fid.  The more common access pattern is to set
query parameters (spatial and attribute), and then to iterate through
those features matching using GetNextFeature().  In this situation there
is none of this "search by fid" O(n*n) performance.

Martin is implementing valuable optimizations for the GetFeature() by
FID case but the other alternative that performs well for all OGR
datasources is to use the GetNextFeature() iterator.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org