Issue Details (XML | Word | Printable)

Key: DOC-93
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Won't Fix
Priority: Minor Minor
Assignee: Paul Vinkenoog
Reporter: Pavel Zotov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Documentation

Improve FAQ info about selectivity and SET STATISTICS INDEX command

Created: 01/Apr/14 01:54 PM   Updated: 29/Dec/14 01:47 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

File Attachments: 1. Zip Archive idx_stat_sample.zip (0.6 kB)



 Description  « Hide
1. The formula in http://www.firebirdfaq.org/faq167/ about selectivity:

--- quote beg ---
Selectivity is quantified by index 'statistics' which is computed like this:

select count(*)/count(distinct(index_field)) from table;
--- quote end ---

What is count(*)/count(distinct(index_field)) ? If it is so called 'statistics' then where we can see it (I mean in RDB tables) ? DOes optimizer uses this number *instead* of selectivity ?
Why not to use simpler formula: "Selectivity is select 1e0 / count(distinct f) from t" ?

2. The SET STATISTICS INDEX command can lead to WRONG results if table has a lot of garbage in it. This command should be run only after GC (e.g gfix -sweep or select count(*) from t).

Please look at the sample script from attach
If we comment out lines with phrase '-- remove garbage!' (this is lines ## 41, 64 & 86) than command "set statistics index t_*" for all three indices can give totally wrong result.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Paul Vinkenoog added a comment - 29/Dec/14 01:45 PM
Sorry, the Firebird FAQ is not maintained by the Firebird project.
Comments on the FAQ should be directed to faq@firebirdfaq.org

Paul Vinkenoog added a comment - 29/Dec/14 01:47 PM
Not a project thing.