[mapserver-users] Speeding up WFS paging with Spatialite and GeoPackage

[Rahkonen Jukka (MML)]

Hi Even,

When Geopackage or Spatialite db are created with ogr2ogr there is also ogc_fid that could be used for fast range queries.

I decided already not to bother and to forget the whole thing because I was just playing with Geopackage without a direct use case. But then I was reading more and it seems that LIMIT + OFFSET is slow also in PostgreSQL for the same reason.  What I see around line 2358 in https://github.com/mapserver/mapserver/blob/branch-7-0/mapogr.cpp makes me think that those are used by Mapserver and that paging with cursor https://www.postgresql.org/docs/9.1/static/sql-declare.html could be faster with PostGIS. What do you think?

-Jukka-



Even Rouault wrote:


On mardi 20 décembre 2016 10:36:55 CET Rahkonen Jukka (MML) wrote:

> Hi,

>

> It seems that because of how SQLite works it is not possible to make fast

> paging for big tables by using simple SQL with LIMIT and OFFSET. Making a

> query as "LIMIT 100 OFFSET 1000000" makes SQLite to read 1000100 rows and

> throw away the first million ones. The bigger the offset the slower the

> query.

>

> I guess that Mapserver must still use LIMIT and OFFSET because they work

> even with combined attribute and spatial filters. But I wonder if it could

> be possible to make a WFS 2.0 stored query for a not so uncommon use case

> of loading pages without other filters from very big tables. This stored

> query would also take count and startindex as inputs but place them into a

> query like

>

> SELECT * FROM table WHERE rowid>startindex AND rowid<=(startindex+count)

>

> An optional parameter could be used for defining some other unique, numeric

> and indexed attribute for paging. This query should work very fine with a

> SQLite table that is freshly created for example with ogr2ogr because then

> rowids start from one and they do not have gaps. If table has been edited

> there may be missing rowids but that is not critical because query would

> just return sometimes less rows than "count".

>

> Unfortunately by reading

> http://mapserver.org/ogc/wfs_server.html#stored-queries-wfs-2-0 it is not

> obvious for me how to write such "GetPageByRowid" query and I would

> appreciate getting some help.



I'm not completely sure it is legal to reuse the standard STARTINDEX and COUNT parameters as query parameters, but that would probably work in the current implementation. But you'll be stuck with the addition of the startindex and count. There's no way to do that in a Filter understood by MapServer. You could however have a startindex and lastindex params. You would need to have the rowid column explictly exposed as a field with something like DATA "SELECT rowid as the_rowid, * FROM table". Hum but then you'd get in the slow path since that wouldn't be recognized as a regular OGR layer.



Even





--

Spatialys - Geospatial professional services

http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161220/a6f3ef66/attachment-0001.html>