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
