[Qgis-developer] Postgis Caching - Enhancement

Tue Apr 1 02:11:38 PDT 2014

Hi Alex,

thanks for your response. I comment in between.

Am 01.04.2014 10:45, schrieb 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.

Well, I am assuming a defined and maintained spatial index on any 
spatial table.
AFAIK a lot of latency results from connecting to the db before doing 
any SELECT. I do not know if and for how long QGIS keeps connections 
open (I remember that UNM mapserver offered a configuration to keep the 
connection open). But, as I said before, my remote (though in the same 
LAN) PostgreSQL server is really fast.

> 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).

total agree, but that's what my users do in order to share and have 
these files backed up. Maybe we should not cache these to teach 'em :-)

> 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.

Ok, this is an extreme case. But on the other hand: If you have a huge 
dataset and try to look at all its features, creating the cache will 
consume some time, too, so it might be even slower at the beginning, 
while gaining speed afterwards, once the cache is in place.

> 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.

It might also be useful to have scale based rules. Or split the data up 
into several relations (either tables or views) thus gaining speed by 
threaded rendering?

>
> 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.

ok, so the cache is getting bigger and bigger as you move around the map?

>
> 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).

another thought: how about only requesting fields necessary for 
labelling/styling, even as a setting in the fields tab ("do not load 
from source"). Not sure if that is useful as I assume that normally 
geometries are responsible for most of the data transfer.

regards

Bernhard

>
> 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
>>>
>>>
>>
>
>
>
> __________ Information from ESET Security, version of virus signature database 9620 (20140401) __________
>
> The message was checked by ESET Security.
>
>      part000.txt - is OK
>
> http://www.eset.com
>
>

-- 
Bernhard Ströbl
Anwendungsbetreuer GIS

Kommunale Immobilien Jena
Am Anger 26
07743 Jena

Tel.: 03641 49- 5190
E-Mail: bernhard.stroebl at jena.de
Internet: www.kij.de

Kommunale Immobilien Jena
Eigenbetrieb der Stadt Jena
Werkleiter: Dr. Götz Blankenburg

__________ Information from ESET Security, version of virus signature database 9620 (20140401) __________

The message was checked by ESET Security.

    part000.txt - is OK

http://www.eset.com