Обсуждение: ALTER SET DISTINCT vs. Oracle-like DBMS_STATS
Hello, A new feature "ALTER TABLE ... ALTER COLUMN ... SET DISTINCT" is submitted to the next commetfest: http://archives.postgresql.org/message-id/603c8f070905041913r667b3f32oa068d758ba5f17e1@mail.gmail.com but I have another approach for the plan stability issues. It might conflict ALTER SET DISTINCT patch in terms of duplicated functionality, so I want to discuss them. It is just similar to Oracle's DBMS_STATS package. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm If it were, "ALTER TABLE tablename ALTER COLUMN 3rd-column SET DISITNCT 100" could be written as: INSERT INTO dbms_stats.columns(starelid, ataattnum, stadistinct) VALUES ('tablename'::regclass, 3, 100); Of course ALTER SET DISTINCT is easy-to-use, so it could be an alias for the above INSERT command. The "DBMS_STATS for Postgres" is based on new statstics hooks in 8.4 -- get_relation_info_hook, get_attavgwidth_hook, get_relation_stats_hook and get_index_stats_hook. The module has dbms_stats.relations and dbms_stats.columns tables and hides pg_class and pg_statistics when enabled. So, if once you set a value to dbms_stats.columns.stadistinct, the value hides pg_statistics.stadistinct and planner always uses it for planning. You can modify statistics of your tables by inserting values directly to relations and columns tables. Also lock() or unlock() functions are useful to use a bit customized stats based on existing values. - TABLE dbms_stats.relations : hide pg_class.relpages, reltuples. - TABLE dbms_stats.columns : hide pg_statistic. - FUNCTION dbms_stats.lock() : copy pg_class and pg_statistic to the above tables. - FUNCTION dbms_stats.unlock(): delete some rows from the above tables. The module also supports backup-statstics feature. - TABLE dbms_stats.backup, relations_backup, columns_backup - FUNCTION dbms_stats.backup() : backup statistics to the above tables. - FUNCTION dbms_stats.restore() : restore statistics from - FUNCTION dbms_stats.export() : export statistics to external text file. - FUNCTION dbms_stats.import() : import statistics from external text file. If acceptable, I'd like to submit DBMS_STATS for Postgres module to September commitfest. I'm not sure the feature should be in core, in contrib, or in pgFoundry... Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Itagaki Takahiro escribió: > It is just similar to Oracle's DBMS_STATS package. > http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm > If it were, "ALTER TABLE tablename ALTER COLUMN 3rd-column SET DISITNCT 100" > could be written as: > > INSERT INTO dbms_stats.columns(starelid, ataattnum, stadistinct) > VALUES ('tablename'::regclass, 3, 100); > > Of course ALTER SET DISTINCT is easy-to-use, so it could be an alias for > the above INSERT command. Why wouldn't you implement this through reloptions? (I ask because the syntax you propose above is awfully similar to what we used for pg_autovacuum, which ended up being replaced by reloptions) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Euler Taveira de Oliveira <euler@timbira.com> wrote: > >> INSERT INTO dbms_stats.columns(starelid, ataattnum, stadistinct) > >> VALUES ('tablename'::regclass, 3, 100); > > > > Why wouldn't you implement this through reloptions? > > > Because it is column-based and not table-based? In this case, we need to store > and array value like {attnum, stadistinct}. If it is not ugly in your POV, +1 > for this approach. Yes, column-based storage is needed. However, when we drop tables, dangling stat settings might remain. I want core-support for the module, for example, "TRIGGER ON DROP TABLE" or some drop-relation-hooks. There might be another approach that we add pg_attribute.attoptions for generic column-based options, like pg_class.reloptions. Which approach is better, or something else? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center