Add TABLE option to SET STATISTICS to recalculate statistics of all indexes of a table

FirebirdSQL / firebird

Firebird server, client and tools

https://www.firebirdsql.org/

1.24k stars 215 forks source link

Add TABLE option to SET STATISTICS to recalculate statistics of all indexes of a table #7669

Open mrotteveel opened 1 year ago

mrotteveel commented 1 year ago

Currently, when you want to recalculate statistics, you need to call SET STATISTICS INDEX <index-name> for each individual index. I think it would be useful to add a SET STATISTICS TABLE <table-name> to recalculate statistics for all indexes of a table.

sim1984 commented 1 year ago

This is a more complex topic than you have described. In the future, statistics may be collected not only for indexes, but also for the tables themselves. Other DBMS have stored statistics on the selectivity of individual columns of the table.

mrotteveel commented 1 year ago

This is a more complex topic than you have described. In the future, statistics may be collected not only for indexes, but also for the tables themselves. Other DBMS have stored statistics on the selectivity of individual columns of the table.

If that ever happens (because I've been hearing this for over a decade), we can modify the clause as follows:

Bare SET STATISTICS TABLE <table-name> recalculates all statistics related to the table (so for indexes, table itself, whatever else gets added in the future)
Add a {TABLE | INDEX} ONLY option (i.e. SET STATISTICS TABLE <table-name> [{TABLE | INDEX} ONLY]), where TABLE recalculates only the table statistics, INDEX only the index statistics, and if we ever add something else like column statistics, we can add that option to the list.

ertankucukoglu commented 2 months ago

I do not remember recalculating just one table index statistics. Whenever I do, I recalculate all indexes in a database. I also think it would be helpful to have ability to do it for all database indexes using a single line of SQL.

TommiPrami commented 2 months ago

It would be actually quite nice to have the possibility to update all indexes in one liner, without needing to bother with the system tables.

Like to disable all trikers and indecies etc... And enable them back.

Would help offline db maintenance and structure changes

sim1984 commented 2 months ago

Collecting statistics may be more complex than just calculating index selectivity. Perhaps we should implement something like DBMS_STATS package as in Oracle.

aafemt commented 2 months ago

Perhaps we should implement something like DBMS_STATS package as in Oracle.

...or (as in Oracle) compare expected stats and real stats got during query execution and adjust them on-the-fly.