[Qgis-developer] Postgis Caching - Enhancement

[Alex Mandel]

This really applies to non-local data sources. Network latency can be
huge issue, it can also be problematic if a bbox strategy isn't in use.
You are right this is not Postgis specific. Database servers are more
likely to be remote (shapefiles on a network share are a terrible idea
in almost all cases). Maybe the recommendation is that people pipe
Postgis through a WFS service when they want to use it this way? If I
think about it that way WMS or WMTS would actually be a good way to get
speed - but you can't really see attribute tables that way. Wonder if
there's some way to hybridize it. Of course this assumes said users have
the ability to setup such services in house quickly for new layers.

I've experienced this with DB tables from 1-100+ GB, it's not as big an
issue with non DB (Oracle, MSSQL, etc) formats because they don't get as
large. The problem is that most people forget to zoom in before turning
on the layer, or if they need to see the whole extent the whole data is
transferred before the renderer can simplify it. It's almost like a set
of postgis views used similar to pyramids in rasters with simplification
algorithms applied before transmission would be a way to handle it. Not
QGIS specific but I could see a plugin in QGIS to build the views in
Postgis.

Panning/Zooming requests data if the data isn't already cached. So if
you start panning then go back to a spot you've been before you already
have it.

I agree that always on caching is bad, hence my suggestion for a user
toggle per layer. When doing cartography work you just need to be able
work a little faster with the rendering for it to not be frustrating.
Same for digitizing (assuming the postgis layer in question is reference).

Thanks,
Alex

On 03/31/2014 11:19 PM, Bernhard Ströbl wrote:
> Hi Alex,
> 
> we are using a PostGIS database here with several clients connected at
> any point in time (geoserver and QGIS users). I am always impressed how
> fast QGIS draws even layers with many features and complex rules. Even
> when working on my old laptop with a local postgreSQL instance (base
> configuration) it is impressively fast. So I would not say that the
> PostGIS provider _in general_ is slow (as [1] suggests). Looking at
> OpenJump: are there configurations where caching really improves speed?
> If I understood the explanations right only those features intersecting
> the viewport are cached, so any panning/zooming results in a database
> request, too.
> Still speed improvements are always welcome, but if a layer is read-only
> for one user this does not neccessarily mean that it is read-only for
> others, too, so care must be taken when to activate caching. Although
> there are definitely layers for which changes cannot be expected during
> one QGIS session, so caching (in memory) might improve performance.
> Looking at other providers I know that shape files from network drives
> are sloooowww. So I would opt for a generic approach that works with any
> provider.
> 
> my 2 cts
> 
> Bernhard
> 
> [1] http://gis.stackexchange.com/q/46967/3183
> 
> Am 01.04.2014 06:51, schrieb Alex Mandel:
>> Searching tickets I don't see this one, but wanted to check that it
>> wasn't on the roadmap.
>>
>> Often when working with Postgis layers, it's a read-only relationship
>> directly to a table or view that is not expected to change anytime soon.
>> To speed up panning/zooming etc we should implement and optional caching
>> mechanism (maybe this is a general feature for all vector providers?).
>>
>> It's one of the only things I've seen OpenJump do that QGIS doesn't do
>> at all. See the Check box in the add table dialog - Cache Features
>> http://sourceforge.net/apps/mediawiki/jump-pilot/index.php?title=Working_with_Databases
>>
>>
>> Topic comes up often:
>> http://gis.stackexchange.com/q/46967/3183
>> There are plenty of previous conversations like this around.
>>
>> If it's not anywhere in the plans I'll be happy to open a ticket.
>>
>> Thanks,
>> Alex
>> _______________________________________________
>> Qgis-developer mailing list
>> Qgis-developer at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>>
>> __________ Information from ESET Security, version of virus signature
>> database 9619 (20140331) __________
>>
>> The message was checked by ESET Security.
>>
>>      part000.txt - is OK
>>
>> http://www.eset.com
>>
>>
>