[Qgis-developer] QEP/RFC sqlite virtual tables

[Hugo Mercier]

Hi Martin,

Le 08/11/2014 07:06, Martin Dobias a écrit :
> Hi Hugo
> 
> On Tue, Nov 4, 2014 at 8:36 PM, Hugo Mercier <hugo.mercier at oslandia.com> wrote:
>>
>> * About indexes on virtual tables, contrary to what I wrote previously,
>> the xBestIndex() method of virtual tables should be enough to orient the
>> planner, an estimated cost and estimated number of rows can be returned
>> for each part of the where clause. So there should be no need to copy
>> native indexes.
>> But the provider interface should be extended in order to provide such
>> statistics.
> 
> I am trying to figure out how the things would work...
> 
> How would you decide whether to copy native index of a virtual table?
> (Always?) E.g. pkey at least in Postgres is also index - shall we
> always copy it?
> When would you make copies of native indexes? (When constructing the
> provider? On every getFeatures() call?)
> How would you keep the index up to date? (Table may change outside of QGIS)
> 
> What I want to understand is how SQL select statements would execute -
> like the one with join filter you need:
> 
> SELECT * FROM tblA LEFT JOIN tblB ON tblA.X = tblB.Y WHERE tblY.Z = 42
> 

The idea is precisely to avoid copy of native indexes.

With this example query, the virtual table implementation of tblA and
tblB will be called (xBestIndex) twice : once with and once without
constraints :
* X = ? on tblA
* Y = ? AND Z = ? on tblB

If you know there is a native index on tblA(X) then you will return a
cost for this constraint that is inferior to the cost needed without
constraint (a seq scan). The planner will decide what to do based on
these costs.

Some discussion here :
http://osdir.com/ml/sqlite-dev/2014-11/msg00003.html

For spatial indexes this is still possible (to avoid copy of native
indexes), but would require to introduce some new syntax to translate
the spatial predicate into regular comparison operators.

See here :
https://www.mail-archive.com/sqlite-users@sqlite.org/msg87191.html

... so for spatial indexes some additional SQL must be generated from
QGIS (but it is already the case if we want to avoid the spatialite
index syntax)

>> We are willing to develop this as a plugin if it can be included in QGIS
>> as a c++ plugin. Is there any objection to this ?
> 
> Personally I do not see much difference in having some functionality
> in 'core' and having some functionality in c++ plugin (included in
> QGIS tree) - in both cases that requires us to maintain such code once
> it is added...
> 
> Why not develop a prototype that does not require QGIS code - I guess
> the only QGIS-specific part is the virtual table implementation. Such
> prototype could eventually evolve to a standalone library providing
> SQL parser / execute engine with a nice interface...
> 

Ok.

It means the optimization part (accessing costs of the native indexes
for example, as discussed above) cannot be demonstrated yet with such an
external prototype, because it would require to modify providers'
interface in the core. But it does not prevent the plugin / library to
take them into account.