Thanks!
What would be the best way to vacuum that table using SQL commands as of 3.33.0 ?
I was thinking of running something like this in a transaction:
create temporary table defrag as select * from A
delete from A
insert into A select * from defrag
drop table defrag
I tried it and it fixed the "unused bytes" ratio, but it apparently reused the just-released non-sequential pages for the "insert", so there is still a very high number of non-sequential pages (analyzer results below), and the performance is still quite lower than table B.
Even on an SSD, the thousands of scattered random accesses take a toll, and it takes 6 seconds with a non-primed SQLite cache to do a "select count(*) from A" for instance (vs 400 ms when primed).
By comparison "select count(*) from B" takes about 400ms with a non-primed cache, and 200 ms when primed.
Would using larger database pages help ? Or would it just compound the problem ?
*** Table A w/o any indices ******************************************
Percentage of total database...................... 0.11%
Number of entries................................. 30963748
Bytes of storage consumed......................... 417886208
Bytes of payload.................................. 176640955 42.3%
Bytes of metadata................................. 240452642 57.5%
B-tree depth...................................... 3
Average payload per entry......................... 5.70
Average unused bytes per entry.................... 0.03
Average metadata per entry........................ 7.77
Average fanout.................................... 334.00
Non-sequential pages.............................. 63580 62.3%
Maximum payload per entry......................... 9
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 305
Primary pages used................................ 101718
Overflow pages used............................... 0
Total pages used.................................. 102023
Unused bytes on index pages....................... 154461 12.4%
Unused bytes on primary pages..................... 638150 0.15%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 792611 0.19%