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

[Even Rouault]

Jukka,

I tried to simulate your scenario but couldn't observe any performance 
difference (on Linux, but I doubt that the OS would make a difference) when 
generating a test DB with 3.7.9 or 3.7.14, and using it with the other 
version.
However it might heavily depend on the number of records that match 
attr_1='009' and area>1000

Could you share your DB ?

Even

> 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