[gdal-dev] GDAL may create slow GPKG/SQLite indexes

[Jukka Rahkonen]

Hi,

I have a table with 1.2 million rows. Attribute 1 has 320 distinct values
(text). Attribute 2 is numeric (area).

My test query is this:

select * from table
where attr_1='009'
or area>1000;

First index makes query a bit faster
ogrinfo test.gpkg -sql "create index test_attr_1_idx on test (attr_1)"

However, after creating a second index the query takes almost 10 times
longer than without the second index
ogrinfo test.gpkg -sql "create index test_area_idx on test (area)"

I can confirm that the slow query time is caused by the indexes by making
the query as

select * from table not indexed
where attr_1='009'
or area>1000;

This time the query runs in the same time than before I started to make indexes.

Then I opened the GPKG database with Spatialite-gui 1.7.1. I dropped both
indexes and created again. This time my test query is very fast both when I
run it with Spatialite-gui or if I fire it from ogrinfo.

Perhaps this has something to do with different SQLite versions included in
GDAL and Spatialite-gui. My GDAL is v.2.0 Win-64 build from gisinternals. 

This kind of behaviour is nasty when planning to build well optimized and
indexed GeoPackage datafiles for the end users.

I have not analyzed if the trouble is in the index created for the area
field on in the way how these two indexed tables behave when they are used
in the same query with OR. However, I managed to create as slow indexes also
from java through the xerial jdbc driver "sqlite-jdbc-3.7.2.jar". 

-Jukka Rahkonen-