/** Disk-Space Utilization Report For /home/kevin/.local/share/varnam/suggestions/ml.vst.learnings
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 75456
Pages in the whole file (calculated).............. 75456
Pages that store data............................. 75456 100.0%
Pages on the freelist (per header)................ 0 0.0%
Pages on the freelist (calculated)................ 0 0.0%
Pages of auto-vacuum overhead..................... 0 0.0%
Number of tables in the database.................. 4
Number of indices................................. 2
Number of defined indices......................... 0
Number of implied indices......................... 2
Size of the file in bytes......................... 309067776
Bytes of user payload stored...................... 203803017 65.9%
*** Page counts for all tables with their indices *****************************
PATTERNS_CONTENT.................................. 57039 75.6%
WORDS............................................. 18414 24.4%
METADATA.......................................... 2 0.003%
SQLITE_MASTER..................................... 1 0.001%
*** Page counts for all tables and indices separately *************************
PATTERNS_CONTENT.................................. 57039 75.6%
WORDS............................................. 9328 12.4%
SQLITE_AUTOINDEX_WORDS_1.......................... 9086 12.0%
METADATA.......................................... 1 0.001%
SQLITE_AUTOINDEX_METADATA_1....................... 1 0.001%
SQLITE_MASTER..................................... 1 0.001%
*** All tables and indices ****************************************************
Percentage of total database...................... 100.0%
Number of entries................................. 9576793
Bytes of storage consumed......................... 309067776
Bytes of payload.................................. 234130047 75.8%
Average payload per entry......................... 24.45
Average unused bytes per entry.................... 4.46
Average fanout.................................... 129.00
Maximum payload per entry......................... 259
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 512
Primary pages used................................ 74944
Overflow pages used............................... 0
Total pages used.................................. 75456
Unused bytes on index pages....................... 347418 16.6%
Unused bytes on primary pages..................... 42410034 13.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 42757452 13.8%
*** All tables ****************************************************************
Percentage of total database...................... 88.0%
Number of entries................................. 8795239
Bytes of storage consumed......................... 271847424
Bytes of payload.................................. 203803495 75.0%
Average payload per entry......................... 23.17
Average unused bytes per entry.................... 4.36
Average fanout.................................... 129.00
Maximum payload per entry......................... 259
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 512
Primary pages used................................ 65857
Overflow pages used............................... 0
Total pages used.................................. 66369
Unused bytes on index pages....................... 347418 16.6%
Unused bytes on primary pages..................... 37970030 14.1%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 38317448 14.1%
*** All indices ***************************************************************
Percentage of total database...................... 12.0%
Number of entries................................. 781554
Bytes of storage consumed......................... 37220352
Bytes of payload.................................. 30326552 81.5%
Average payload per entry......................... 38.80
Average unused bytes per entry.................... 5.68
Maximum payload per entry......................... 256
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 9087
Overflow pages used............................... 0
Total pages used.................................. 9087
Unused bytes on primary pages..................... 4440004 11.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4440004 11.9%
*** Table METADATA and all its indices ****************************************
Percentage of total database...................... 0.003%
Number of entries................................. 0
Bytes of storage consumed......................... 8192
Bytes of payload.................................. 0 0.0%
Average payload per entry......................... 0.0
Average unused bytes per entry.................... 0.0
Maximum payload per entry......................... 0
Entries that use overflow......................... 0
Primary pages used................................ 2
Overflow pages used............................... 0
Total pages used.................................. 2
Unused bytes on primary pages..................... 8176 99.80%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 8176 99.80%
*** Table METADATA w/o any indices ********************************************
Percentage of total database...................... 0.001%
Number of entries................................. 0
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 0 0.0%
Average payload per entry......................... 0.0
Average unused bytes per entry.................... 0.0
Maximum payload per entry......................... 0
Entries that use overflow......................... 0
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4088 99.80%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4088 99.80%
*** Index SQLITE_AUTOINDEX_METADATA_1 of table METADATA ***********************
Percentage of total database...................... 0.001%
Number of entries................................. 0
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 0 0.0%
Average payload per entry......................... 0.0
Average unused bytes per entry.................... 0.0
Maximum payload per entry......................... 0
Entries that use overflow......................... 0
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4088 99.80%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4088 99.80%
*** Table PATTERNS_CONTENT ****************************************************
Percentage of total database...................... 75.6%
Number of entries................................. 8013680
Bytes of storage consumed......................... 233631744
Bytes of payload.................................. 170964281 73.2%
Average payload per entry......................... 21.33
Average unused bytes per entry.................... 4.71
Average fanout.................................... 116.00
Non-sequential pages.............................. 15220 26.7%
Maximum payload per entry......................... 94
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 488
Primary pages used................................ 56551
Overflow pages used............................... 0
Total pages used.................................. 57039
Unused bytes on index pages....................... 332939 16.7%
Unused bytes on primary pages..................... 37439370 16.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 37772309 16.2%
*** Table SQLITE_MASTER *******************************************************
Percentage of total database...................... 0.001%
Number of entries................................. 5
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 478 11.7%
Average payload per entry......................... 95.60
Average unused bytes per entry.................... 697.60
Maximum payload per entry......................... 177
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3488 85.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3488 85.2%
*** Table WORDS and all its indices *******************************************
Percentage of total database...................... 24.4%
Number of entries................................. 1563108
Bytes of storage consumed......................... 75423744
Bytes of payload.................................. 63165288 83.7%
Average payload per entry......................... 40.41
Average unused bytes per entry.................... 3.18
Average fanout.................................... 388.00
Maximum payload per entry......................... 259
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 24
Primary pages used................................ 18390
Overflow pages used............................... 0
Total pages used.................................. 18414
Unused bytes on index pages....................... 14479 14.7%
Unused bytes on primary pages..................... 4959000 6.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4973479 6.6%
*** Table WORDS w/o any indices ***********************************************
Percentage of total database...................... 12.4%
Number of entries................................. 781554
Bytes of storage consumed......................... 38207488
Bytes of payload.................................. 32838736 85.9%
Average payload per entry......................... 42.02
Average unused bytes per entry.................... 0.69
Average fanout.................................... 388.00
Non-sequential pages.............................. 9284 99.54%
Maximum payload per entry......................... 259
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 24
Primary pages used................................ 9304
Overflow pages used............................... 0
Total pages used.................................. 9328
Unused bytes on index pages....................... 14479 14.7%
Unused bytes on primary pages..................... 523084 1.4%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 537563 1.4%
*** Index SQLITE_AUTOINDEX_WORDS_1 of table WORDS *****************************
Percentage of total database...................... 12.0%
Number of entries................................. 781554
Bytes of storage consumed......................... 37216256
Bytes of payload.................................. 30326552 81.5%
Average payload per entry......................... 38.80
Average unused bytes per entry.................... 5.68
Non-sequential pages.............................. 8945 98.5%
Maximum payload per entry......................... 256
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 9086
Overflow pages used............................... 0
Total pages used.................................. 9086
Unused bytes on primary pages..................... 4435916 11.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4435916 11.9%
*** Definitions ***************************************************************
Page size in bytes
The number of bytes in a single page of the database file.
Usually 1024.
Number of pages in the whole file
The number of 4096-byte pages that go into forming the complete
database
Pages that store data
The number of pages that store data, either as primary B*Tree pages or
as overflow pages. The number at the right is the data pages divided by
the total number of pages in the file.
Pages on the freelist
The number of pages that are not currently in use but are reserved for
future use. The percentage at the right is the number of freelist pages
divided by the total number of pages in the file.
Pages of auto-vacuum overhead
The number of pages that store data used by the database to facilitate
auto-vacuum. This is zero for databases that do not support auto-vacuum.
Number of tables in the database
The number of tables in the database, including the SQLITE_MASTER table
used to store schema information.
Number of indices
The total number of indices in the database.
Number of defined indices
The number of indices created using an explicit CREATE INDEX statement.
Number of implied indices
The number of indices used to implement PRIMARY KEY or UNIQUE constraints
on tables.
Size of the file in bytes
The total amount of disk space used by the entire database files.
Bytes of user payload stored
The total number of bytes of user payload stored in the database. The
schema information in the SQLITE_MASTER table is not counted when
computing this number. The percentage at the right shows the payload
divided by the total file size.
Percentage of total database
The amount of the complete database file that is devoted to storing
information described by this category.
Number of entries
The total number of B-Tree key/value pairs stored under this category.
Bytes of storage consumed
The total amount of disk space required to store all B-Tree entries
under this category. The is the total number of pages used times
the pages size.
Bytes of payload
The amount of payload stored under this category. Payload is the data
part of table entries and the key part of index entries. The percentage
at the right is the bytes of payload divided by the bytes of storage
consumed.
Average payload per entry
The average amount of payload on each entry. This is just the bytes of
payload divided by the number of entries.
Average unused bytes per entry
The average amount of free space remaining on all pages under this
category on a per-entry basis. This is the number of unused bytes on
all pages divided by the number of entries.
Non-sequential pages
The number of pages in the table or index that are out of sequence.
Many filesystems are optimized for sequential file access so a small
number of non-sequential pages might result in faster queries,
especially for larger database files that do not fit in the disk cache.
Note that after running VACUUM, the root page of each table or index is
at the beginning of the database file and all other pages are in a
separate part of the database file, resulting in a single non-
sequential page.
Maximum payload per entry
The largest payload size of any entry.
Entries that use overflow
The number of entries that user one or more overflow pages.
Total pages used
This is the number of pages used to hold all information in the current
category. This is the sum of index, primary, and overflow pages.
Index pages used
This is the number of pages in a table B-tree that hold only key (rowid)
information and no data.
Primary pages used
This is the number of B-tree pages that hold both key and data.
Overflow pages used
The total number of overflow pages used for this category.
Unused bytes on index pages
The total number of bytes of unused space on all index pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on index pages.
Unused bytes on primary pages
The total number of bytes of unused space on all primary pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on primary pages.
Unused bytes on overflow pages
The total number of bytes of unused space on all overflow pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on overflow pages.
Unused bytes on all pages
The total number of bytes of unused space on all primary and overflow
pages. The percentage at the right is the number of unused bytes
divided by the total number of bytes.
*******************************************************************************
The entire text of this report can be sourced into any SQL database
engine for further analysis. All of the text above is an SQL comment.
The data used to generate this report follows:
*/
BEGIN;
CREATE TABLE space_used(
name clob, -- Name of a table or index in the database file
tblname clob, -- Name of associated table
is_index boolean, -- TRUE if it is an index, false for a table
nentry int, -- Number of entries in the BTree
leaf_entries int, -- Number of leaf entries
payload int, -- Total amount of data stored in this table or index
ovfl_payload int, -- Total amount of data stored on overflow pages
ovfl_cnt int, -- Number of entries that use overflow
mx_payload int, -- Maximum payload size
int_pages int, -- Number of interior pages used
leaf_pages int, -- Number of leaf pages used
ovfl_pages int, -- Number of overflow pages used
int_unused int, -- Number of unused bytes on interior pages
leaf_unused int, -- Number of unused bytes on primary pages
ovfl_unused int, -- Number of unused bytes on overflow pages
gap_cnt int, -- Number of gaps in the page layout
compressed_size int -- Total bytes stored on disk
);
INSERT INTO space_used VALUES('sqlite_master','sqlite_master',0,5,5,478,0,0,177,0,1,0,0,3488,0,0,4096);
INSERT INTO space_used VALUES('metadata','metadata',0,0,0,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_metadata_1','metadata',1,0,0,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('words','words',0,790857,781554,32838736,0,0,259,24,9304,0,14479,523084,0,9284,38207488);
INSERT INTO space_used VALUES('sqlite_autoindex_words_1','words',1,781554,781554,30326552,0,0,256,0,9086,0,0,4435916,0,8945,37216256);
INSERT INTO space_used VALUES('patterns_content','patterns_content',0,8070230,8013680,170964281,0,0,94,488,56551,0,332939,37439370,0,15220,233631744);
COMMIT;