Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Dominique Devienne
On Mon, Jul 30, 2018 at 11:42 AM Eric Grange wrote: > PtrMap pages may be too much overhead in my case, I have occasionally run > vacuum on same databases to see the effect, and it was not very > significant. > > This is likely because the databases are heavily skewed towards inserting > (and

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
PtrMap pages may be too much overhead in my case, I have occasionally run vacuum on same databases to see the effect, and it was not very significant. This is likely because the databases are heavily skewed towards inserting (and indexing) data than about update/delete, and while the tables are

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Dominique Devienne
Oops, sent too early... On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne wrote: > On Mon, Jul 30, 2018 at 10:11 AM Eric Grange wrote: > >> @David Yip >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you >> > can query the dbstat virtual table for the number of pages

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Dominique Devienne
On Mon, Jul 30, 2018 at 10:11 AM Eric Grange wrote: > @David Yip > > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you > > can query the dbstat virtual table for the number of pages used per > table and index, e.g. > > Thanks, I did not know about that module, however it

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
@David Yip > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you > can query the dbstat virtual table for the number of pages used per table and index, e.g. Thanks, I did not know about that module, however it is a bit slow, on a 7 GB database I have, it takes almost 5

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread D Burgess
download sqlite3_analyzer from http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange wrote: > Hi, > > Is there a quick way (as in speed) to obtain the number of database blocks > (or kilobytes) allocated for each table and each index ? > >

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread David Yip
Hi Eric, If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you can query the dbstat virtual table for the number of pages used per table and index, e.g. SELECT name, COUNT(path) AS pages FROM dbstat GROUP BY name ORDER BY pages DESC; This runs in ~600 ms on one of my

[sqlite] Quick way to figure SQLite database block size per table & indexes

2018-07-30 Thread Eric Grange
Hi, Is there a quick way (as in speed) to obtain the number of database blocks (or kilobytes) allocated for each table and each index ? I have been using various manual approaches so far (from using length() to vacuum and drops), but none of them are really fast or can practical to automate.