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

[Even Rouault]

Hi,

This should hopefully be pretty much independant of GDAL itself, but rather 
tied to the sqlite version used.

I can see that gisinternals builds use sqlite 3.7.9. In the news of version 
3.7.14 I read "And the query planner has been enhanced to better use covering 
indices on queries that use OR terms in the WHERE clause. "

So that might be it.

If you try with 32bit binaries, you could download 
https://www.sqlite.org/2014/sqlite-dll-win32-x86-3080500.zip and likely 
replace the existing bin/sqlite3.dll of gisinternals from the one of 3.8.5 to 
see if things are better.

But from your description, it would seem that what is important is the version 
used to generate the index, and not that much the one that runs the query. 
Which is rather intriguing...

Even

> 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-
> 
> 
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
Geospatial professional services
http://even.rouault.free.fr/services.html