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@lists.osgeo.org > > http://lists.osgeo.org/mailman/listinfo/gdal-dev -- Geospatial professional services http://even.rouault.free.fr/services.html _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev
