SQLite

Check-in [dc37750d4e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dc37750d4e87d0c529785adceeebd838b8f8591d
User & Date: dan 2016-05-06 15:16:02.765
Context
2016-05-06
16:30
Fix an error in main.mk causing testfixture to use individual fts5 source code files instead of fts5.c. (check-in: 933a1c818c user: dan tags: trunk)
15:16
Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables. (check-in: dc37750d4e user: dan tags: trunk)
2016-05-05
11:53
Renumber internal constants in the printf() implemention for a small performance improvement. (check-in: 69d11447f4 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to tool/spaceanal.tcl.
148
149
150
151
152
153
154

155
156
157
158
159
160
161
# queries the in-memory db to produce the space-analysis report.
#
sqlite3 mem :memory:
set tabledef {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
   depth int,        -- Depth of the b-tree
   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







>







148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
# queries the in-memory db to produce the space-analysis report.
#
sqlite3 mem :memory:
set tabledef {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
   is_without_rowid boolean, -- TRUE if WITHOUT ROWID table  
   nentry int,       -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   depth int,        -- Depth of the b-tree
   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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
set isCompressed 0
set compressOverhead 0
set depth 0
set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {

  set is_index [expr {$name!=$tblname}]
  set idx_btree [expr {$is_index || [is_without_rowid $name]}]
  db eval {
    SELECT 
      sum(ncell) AS nentry,
      sum((pagetype=='leaf')*ncell) AS leaf_entries,
      sum(payload) AS payload,
      sum((pagetype=='overflow') * payload) AS ovfl_payload,
      sum(path LIKE '%+000000') AS ovfl_cnt,







|







181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
set isCompressed 0
set compressOverhead 0
set depth 0
set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {

  set is_index [expr {$name!=$tblname}]
  set is_without_rowid [is_without_rowid $name]
  db eval {
    SELECT 
      sum(ncell) AS nentry,
      sum((pagetype=='leaf')*ncell) AS leaf_entries,
      sum(payload) AS payload,
      sum((pagetype=='overflow') * payload) AS ovfl_payload,
      sum(path LIKE '%+000000') AS ovfl_cnt,
231
232
233
234
235
236
237

238
239
240
241
242
243
244
    set prev $pageno
  }
  mem eval {
    INSERT INTO space_used VALUES(
      $name,
      $tblname,
      $is_index,

      $nentry,
      $leaf_entries,
      $depth,
      $payload,     
      $ovfl_payload,
      $ovfl_cnt,   
      $mx_payload,







>







232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
    set prev $pageno
  }
  mem eval {
    INSERT INTO space_used VALUES(
      $name,
      $tblname,
      $is_index,
      $is_without_rowid,
      $nentry,
      $leaf_entries,
      $depth,
      $payload,     
      $ovfl_payload,
      $ovfl_cnt,   
      $mx_payload,
326
327
328
329
330
331
332
333
334
335
336
337

338


339
340
341
342
343
344
345

  # Query the in-memory database for the sum of various statistics 
  # for the subset of tables/indices identified by the WHERE clause in
  # $where. Note that even if the WHERE clause matches no rows, the
  # following query returns exactly one row (because it is an aggregate).
  #
  # The results of the query are stored directly by SQLite into local 
  # variables (i.e. $nentry, $nleaf etc.).
  #
  mem eval "
    SELECT
      int(sum(nentry)) AS nentry,

      int(sum(leaf_entries)) AS nleaf,


      int(sum(payload)) AS payload,
      int(sum(ovfl_payload)) AS ovfl_payload,
      max(mx_payload) AS mx_payload,
      int(sum(ovfl_cnt)) as ovfl_cnt,
      int(sum(leaf_pages)) AS leaf_pages,
      int(sum(int_pages)) AS int_pages,
      int(sum(ovfl_pages)) AS ovfl_pages,







|



|
>
|
>
>







328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350

  # Query the in-memory database for the sum of various statistics 
  # for the subset of tables/indices identified by the WHERE clause in
  # $where. Note that even if the WHERE clause matches no rows, the
  # following query returns exactly one row (because it is an aggregate).
  #
  # The results of the query are stored directly by SQLite into local 
  # variables (i.e. $nentry, $payload etc.).
  #
  mem eval "
    SELECT
      int(sum(
        CASE WHEN (is_without_rowid OR is_index) THEN nentry 
             ELSE leaf_entries 
        END
      )) AS nentry,
      int(sum(payload)) AS payload,
      int(sum(ovfl_payload)) AS ovfl_payload,
      max(mx_payload) AS mx_payload,
      int(sum(ovfl_cnt)) as ovfl_cnt,
      int(sum(leaf_pages)) AS leaf_pages,
      int(sum(int_pages)) AS int_pages,
      int(sum(ovfl_pages)) AS ovfl_pages,
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
  # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
  #
  set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
  set total_pages_percent [percent $total_pages $file_pgcnt]
  set storage [expr {$total_pages*$pageSize}]
  set payload_percent [percent $payload $storage {of storage consumed}]
  set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
  set avg_payload [divide $payload $nleaf]
  set avg_unused [divide $total_unused $nleaf]
  if {$int_pages>0} {
    # TODO: Is this formula correct?
    set nTab [mem eval "
      SELECT count(*) FROM (
          SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
      )
    "]
    set avg_fanout [mem eval "
      SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
          WHERE $where
    "]
    set avg_fanout [format %.2f $avg_fanout]
  }
  set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]

  # Print out the sub-report statistics.
  #
  statline {Percentage of total database} $total_pages_percent
  statline {Number of entries} $nleaf
  statline {Bytes of storage consumed} $storage
  if {$compressed_size!=$storage} {
    set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
    set pct [expr {$compressed_size*100.0/$storage}]
    set pct [format {%5.1f%%} $pct]
    statline {Bytes used after compression} $compressed_size $pct
  }







|
|













|




|







376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
  # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
  #
  set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
  set total_pages_percent [percent $total_pages $file_pgcnt]
  set storage [expr {$total_pages*$pageSize}]
  set payload_percent [percent $payload $storage {of storage consumed}]
  set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
  set avg_payload [divide $payload $nentry]
  set avg_unused [divide $total_unused $nentry]
  if {$int_pages>0} {
    # TODO: Is this formula correct?
    set nTab [mem eval "
      SELECT count(*) FROM (
          SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
      )
    "]
    set avg_fanout [mem eval "
      SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
          WHERE $where
    "]
    set avg_fanout [format %.2f $avg_fanout]
  }
  set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}]

  # Print out the sub-report statistics.
  #
  statline {Percentage of total database} $total_pages_percent
  statline {Number of entries} $nentry
  statline {Bytes of storage consumed} $storage
  if {$compressed_size!=$storage} {
    set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
    set pct [expr {$compressed_size*100.0/$storage}]
    set pct [format {%5.1f%%} $pct]
    statline {Bytes used after compression} $compressed_size $pct
  }