/** 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)................ 138039 Pages in the whole file (calculated).............. 138039 Pages that store data............................. 138039 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.................. 5 Number of indices................................. 4 Number of defined indices......................... 0 Number of implied indices......................... 4 Size of the file in bytes......................... 565407744 Bytes of user payload stored...................... 203803054 36.0% *** Page counts for all tables with their indices ***************************** PATTERNS_CONTENT.................................. 119626 86.7% WORDS............................................. 18408 13.3% METADATA.......................................... 2 0.001% PATTERMNS_CONTENT................................. 2 0.001% SQLITE_MASTER..................................... 1 0.0% *** Page counts for all tables and indices separately ************************* SQLITE_AUTOINDEX_PATTERNS_CONTENT_1............... 64161 46.5% PATTERNS_CONTENT.................................. 55465 40.2% WORDS............................................. 9323 6.8% SQLITE_AUTOINDEX_WORDS_1.......................... 9085 6.6% METADATA.......................................... 1 0.0% PATTERMNS_CONTENT................................. 1 0.0% SQLITE_AUTOINDEX_METADATA_1....................... 1 0.0% SQLITE_AUTOINDEX_PATTERMNS_CONTENT_1.............. 1 0.0% SQLITE_MASTER..................................... 1 0.0% *** All tables and indices **************************************************** Percentage of total database...................... 100.0% Number of entries................................. 17703576 Bytes of storage consumed......................... 565407744 Bytes of payload.................................. 429272479 75.9% Average payload per entry......................... 24.25 Average unused bytes per entry.................... 2.74 Average fanout.................................... 368.00 Maximum payload per entry......................... 259 Entries that use overflow......................... 0 0.0% Index pages used.................................. 176 Primary pages used................................ 137863 Overflow pages used............................... 0 Total pages used.................................. 138039 Unused bytes on index pages....................... 95890 13.3% Unused bytes on primary pages..................... 48412023 8.6% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 48507913 8.6% *** All tables **************************************************************** Percentage of total database...................... 46.9% Number of entries................................. 8851792 Bytes of storage consumed......................... 265383936 Bytes of payload.................................. 203803814 76.8% Average payload per entry......................... 23.02 Average unused bytes per entry.................... 0.16 Average fanout.................................... 368.00 Maximum payload per entry......................... 259 Entries that use overflow......................... 0 0.0% Index pages used.................................. 176 Primary pages used................................ 64615 Overflow pages used............................... 0 Total pages used.................................. 64791 Unused bytes on index pages....................... 95890 13.3% Unused bytes on primary pages..................... 1291290 0.49% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 1387180 0.52% *** All indices *************************************************************** Percentage of total database...................... 53.1% Number of entries................................. 8851784 Bytes of storage consumed......................... 300023808 Bytes of payload.................................. 225468665 75.2% Average payload per entry......................... 25.47 Average unused bytes per entry.................... 5.32 Maximum payload per entry......................... 256 Entries that use overflow......................... 0 0.0% Primary pages used................................ 73248 Overflow pages used............................... 0 Total pages used.................................. 73248 Unused bytes on primary pages..................... 47120733 15.7% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 47120733 15.7% *** Table METADATA and all its indices **************************************** Percentage of total database...................... 0.001% 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.0% 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.0% 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 PATTERMNS_CONTENT and all its indices ******************************* Percentage of total database...................... 0.001% 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 PATTERMNS_CONTENT w/o any indices *********************************** Percentage of total database...................... 0.0% 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_PATTERMNS_CONTENT_1 of table PATTERMNS_CONTENT ***** Percentage of total database...................... 0.0% 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 and all its indices ******************************** Percentage of total database...................... 86.7% Number of entries................................. 16140460 Bytes of storage consumed......................... 489988096 Bytes of payload.................................. 366106431 74.7% Average payload per entry......................... 22.68 Average unused bytes per entry.................... 2.70 Average fanout.................................... 364.00 Maximum payload per entry......................... 97 Entries that use overflow......................... 0 0.0% Index pages used.................................. 152 Primary pages used................................ 119474 Overflow pages used............................... 0 Total pages used.................................. 119626 Unused bytes on index pages....................... 81366 13.1% Unused bytes on primary pages..................... 43458002 8.9% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 43539368 8.9% *** Table PATTERNS_CONTENT w/o any indices ************************************ Percentage of total database...................... 40.2% Number of entries................................. 8070230 Bytes of storage consumed......................... 227184640 Bytes of payload.................................. 170964318 75.3% Average payload per entry......................... 21.18 Average unused bytes per entry.................... 0.11 Average fanout.................................... 364.00 Non-sequential pages.............................. 46194 83.3% Maximum payload per entry......................... 94 Entries that use overflow......................... 0 0.0% Index pages used.................................. 152 Primary pages used................................ 55313 Overflow pages used............................... 0 Total pages used.................................. 55465 Unused bytes on index pages....................... 81366 13.1% Unused bytes on primary pages..................... 777277 0.34% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 858643 0.38% *** Index SQLITE_AUTOINDEX_PATTERNS_CONTENT_1 of table PATTERNS_CONTENT ******* Percentage of total database...................... 46.5% Number of entries................................. 8070230 Bytes of storage consumed......................... 262803456 Bytes of payload.................................. 195142113 74.3% Average payload per entry......................... 24.18 Average unused bytes per entry.................... 5.29 Non-sequential pages.............................. 44857 69.9% Maximum payload per entry......................... 97 Entries that use overflow......................... 0 0.0% Primary pages used................................ 64161 Overflow pages used............................... 0 Total pages used.................................. 64161 Unused bytes on primary pages..................... 42680725 16.2% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 42680725 16.2% *** Table SQLITE_MASTER ******************************************************* Percentage of total database...................... 0.0% Number of entries................................. 8 Bytes of storage consumed......................... 4096 Bytes of payload.................................. 760 18.6% Average payload per entry......................... 95.00 Average unused bytes per entry.................... 399.12 Maximum payload per entry......................... 167 Entries that use overflow......................... 0 0.0% Primary pages used................................ 1 Overflow pages used............................... 0 Total pages used.................................. 1 Unused bytes on primary pages..................... 3193 78.0% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 3193 78.0% *** Table WORDS and all its indices ******************************************* Percentage of total database...................... 13.3% Number of entries................................. 1563108 Bytes of storage consumed......................... 75399168 Bytes of payload.................................. 63165288 83.8% Average payload per entry......................... 40.41 Average unused bytes per entry.................... 3.17 Average fanout.................................... 388.00 Maximum payload per entry......................... 259 Entries that use overflow......................... 0 0.0% Index pages used.................................. 24 Primary pages used................................ 18384 Overflow pages used............................... 0 Total pages used.................................. 18408 Unused bytes on index pages....................... 14524 14.8% Unused bytes on primary pages..................... 4934476 6.6% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 4949000 6.6% *** Table WORDS w/o any indices *********************************************** Percentage of total database...................... 6.8% Number of entries................................. 781554 Bytes of storage consumed......................... 38187008 Bytes of payload.................................. 32838736 86.0% Average payload per entry......................... 42.02 Average unused bytes per entry.................... 0.66 Average fanout.................................... 388.00 Non-sequential pages.............................. 9296 99.72% Maximum payload per entry......................... 259 Entries that use overflow......................... 0 0.0% Index pages used.................................. 24 Primary pages used................................ 9299 Overflow pages used............................... 0 Total pages used.................................. 9323 Unused bytes on index pages....................... 14524 14.8% Unused bytes on primary pages..................... 502644 1.3% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 517168 1.4% *** Index SQLITE_AUTOINDEX_WORDS_1 of table WORDS ***************************** Percentage of total database...................... 6.6% Number of entries................................. 781554 Bytes of storage consumed......................... 37212160 Bytes of payload.................................. 30326552 81.5% Average payload per entry......................... 38.80 Average unused bytes per entry.................... 5.67 Non-sequential pages.............................. 8951 98.5% Maximum payload per entry......................... 256 Entries that use overflow......................... 0 0.0% Primary pages used................................ 9085 Overflow pages used............................... 0 Total pages used.................................. 9085 Unused bytes on primary pages..................... 4431832 11.9% Unused bytes on overflow pages.................... 0 Unused bytes on all pages......................... 4431832 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,8,8,760,0,0,167,0,1,0,0,3193,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,790852,781554,32838736,0,0,259,24,9299,0,14524,502644,0,9296,38187008); INSERT INTO space_used VALUES('sqlite_autoindex_words_1','words',1,781554,781554,30326552,0,0,256,0,9085,0,0,4431832,0,8951,37212160); INSERT INTO space_used VALUES('patterns_content','patterns_content',0,8125542,8070230,170964318,0,0,94,152,55313,0,81366,777277,0,46194,227184640); INSERT INTO space_used VALUES('sqlite_autoindex_patterns_content_1','patterns_content',1,8070230,8070230,195142113,0,0,97,0,64161,0,0,42680725,0,44857,262803456); INSERT INTO space_used VALUES('pattermns_content','pattermns_content',0,0,0,0,0,0,0,0,1,0,0,4088,0,0,4096); INSERT INTO space_used VALUES('sqlite_autoindex_pattermns_content_1','pattermns_content',1,0,0,0,0,0,0,0,1,0,0,4088,0,0,4096); COMMIT;