[postgis-devel] RE : Is postgres - postgis able to support estimated metadata for views?

[HAUBOURG]

Hi Sandro, 
thanks for your inputs. 

Concerning web servers, you're right mapserver is OK here.

QGIS server probably asks too much, because it inherits from desktop world. As it has WFST transactional capabilities, I think it still requires to scan data to ensure unicity of primary keys. 
Agreed that geometries should not be parsed to  find geometry types, we probably did not declare yet all our geometries typemod. I have to check that. 

Concerning GIS desktop, that slow loading of views matter still remains. Users do not complain yet but I think we could speed up things a lot (4x times faster at least I guess). My point is that datasets grow when years pass, more GPS everywhere, more precision.. One day, working with views on huge datasets will be a real bottleneck for desktop GIS. Users facing it today are very few, this might change quickly.

I see some possible dirty workarounds:
    - Give more resources to postgres server. Maybe a linear 20% of loading time improvement a year ? Not enough to handle the growth curve  of datasets, which is exponential

    - Split datasets from views to table. This is very costy for DBA and GIS administrator, and quite a regression back to "file" model we had before spatial DB era. 

    - Make materialized views: . Do materialized view have statistics? That still needs some CRON on "refresh view", and this eats disk space (not so affordable for securized virtual bays - see how much it costs to store Terabytes in the cloud). It is also tricky actually when pgadmin do not show indexes in DDL, it happen that we drop them we changing SQL definition, forgetting the indexes.

   - Change QGIS code to let it load a view without checking metadata. That is really error prone. I can ask QGIS dev's... 

So, none of these workarounds seem able to handle what big data is preparing for the next few years.  Anyone knows if handling metadata on views in postgres is impossible by design, or if it is a interesting path to explore? 

Cheer

________________________________________
De : Sandro Santilli [sandro.santilli at gmail.com] de la part de Sandro Santilli [strk at keybit.net]
Date d'envoi : samedi 12 septembre 2015 13:12
À : HAUBOURG
Cc: postgis-devel at lists.osgeo.org
Objet : Re: [postgis-devel] Is postgres - postgis able to support estimated metadata for views?

On Fri, Sep 11, 2015 at 08:00:42PM +0000, HAUBOURG wrote:

> QGIS / qgis server clients require to extract metadatas such as estimated extent, fields and data types, primary keys and geometry types. This is rocket fast on tables with metadata's updated with autovacuum.
>
> Views do not support such metadatas, then those information require to scan fields, and geometries. Loading layers from views starts to be a severe bottleneck here with increasing spatial resolution of datasets.
>
> QGIS desktop slows down when loading views only, so user have to wait 10 -20 seconds once only for medium size projects, when QGIS server seems to query those metadata on each tile query, and is fairly unusable woth such views. Mapserver does not seem to need those information, and runs smoothly.
>
> My question now:
> Is there any technical option available in Postgres / Postgis architecture to head forward handling views metadata ?
> If possible, could you give me some tips on how to have it land in the project.
> I should be able to fund developers on that - no free work asked her !

You should check but I believe that mapserver doesn't query for
metadata at all. I'd guess what you're reporting is more a problem
with QGIS than with PostGIS.

Views in PostGIS can very well expose geometry type and SRID
metadata (they are encoded in the geometry typmod).
As for extent, a tile server should know about that upfront,
why would it need to determine it ?

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://cp.mcafee.com/d/k-Kr3zqb32tTT6mrCXCQrzASjhOOMqem3qdNOr9EVpsjudET79ICzBBwQsLCQrzAPhOeuvd7b1KfApkUDHp2Hsi8a_mO-Elryn8X6I8vPIjvWSnR2HsiV7oRx3-tyrd7bPRS7nT-LOb2bOqfnKnjp7fYCMUOyDR4mlHFGThpVkffGhBrwqrhdECXYDuZXTLuZPtPo0eAUBua-m8iDMdODNcOJZe1NKCMgznGr2szPV0Ey9YKroKyYr1vF6y0QJHlFczaxt3PfDCBQQg1J3h0JmAOcvEq82VEwmHip6l3h1F0_Ph1eFEwESIJmcPh1Z9OlSvE6y1nVWxIZd43JoCy0PbP52hJUsrsXKl