Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/
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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Montag, 02. September 2019 13:50 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/ On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter 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 WHERE rowid IN (SELECT rowid FROM > WHERE ); > 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 SELECT rowid,; 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 sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/
On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter 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 WHERE rowid IN (SELECT rowid FROM > WHERE ); > 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 SELECT rowid,; 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: http://roaringbitmap.org/
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 WHERE rowid IN (SELECT rowid FROM WHERE ); provided that the data had been inserted before by running INSERT INTO SELECT rowid,; -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Montag, 02. September 2019 10:59 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] http://roaringbitmap.org/ On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch wrote: > Hi, I think that SQLite use some bitmap indexes Not that I know of, but I don't know the full source code. Maybe FTS[345] do/es, but SQLite itself only uses BTree-indexes AFAIK. > and this here might be of interest if not already used/known: > http://roaringbitmap.org/ I think it’s from the same guy how did SIMDJSON. > Thanks for sharing. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
