You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
--- 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.
The text was updated successfully, but these errors were encountered:
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.
The text was updated successfully, but these errors were encountered: