Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improve FAQ info about selectivity and SET STATISTICS INDEX command [DOC93] #103

Closed
firebird-automations opened this issue Apr 1, 2014 · 4 comments

Comments

@firebird-automations
Copy link

Submitted by: @pavel-zotov

Attachments:
idx_stat_sample.zip

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.

@firebird-automations
Copy link
Author

Modified by: @pavel-zotov

Attachment: idx_stat_sample.zip [ 12470 ]

@firebird-automations
Copy link
Author

Commented by: @paulvink

Sorry, the Firebird FAQ is not maintained by the Firebird project.
Comments on the FAQ should be directed to mailto:faq@firebirdfaq.org

@firebird-automations
Copy link
Author

Commented by: @paulvink

Not a project thing.

@firebird-automations
Copy link
Author

Modified by: @paulvink

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants