The base table is also a virtual table (we have nearly no native SQLite tables) that stores variable length, variable content logfiles and supports access via record offset, serial number and stored datetime. The effort of decoding specific attributes is significant (sequential read and decode is 10% CPU and 90% IO bound). Certain well defined and commonly used discrete attributes (e.g. ACK/NAK, transaction type, retailer number,..) were lifted from the records via batch programs and inserted into the bitmap index. This reduced the numbe rof records to be read from disk and decoded by a factor of about 1000-4000 with proportional performance gains.
Cardinality of columns ranged from 2 (true/false) to several thousand; the software does a very good job of compressing and processing the bitmaps, leading to high performance. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Dominique Devienne Gesendet: Montag, 02. September 2019 13:50 An: SQLite mailing list <[email protected]> Betreff: Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/ On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter <[email protected]> wrote: > Back in 2011 I implemented a virtual table using the "fastbit" library > by John Wu of the Lawrence Berekely National Laboratory. This allowed > selects of the form > > SELECT ... FROM <base_table> WHERE rowid IN (SELECT rowid FROM > <fastbit_index> WHERE <constraints>); > Did it work well? Did you get any speedup compared to a normal BTree index? Available anywhere? How low the cardinality of indexed columns value-space needs to be to benefit from a bitmap index? > provided that the data had been inserted before by running > > INSERT INTO <fastbit_index> SELECT rowid,<indexed fields>; Custom (user-defined) indexes is an area that I'd welcome in SQLite. You can work around it as you did above, but that implies the index maintenance rests on the user's shoulders. While it would be relatively easy I suspect for SQLite core to notify a custom index of table changes. Conversely, you can't use SQLite (sole for now) BTree indexes with a virtual table, AFAIK, (I have a doubt all of a sudden...), the vtable must do all the indexing itself. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
