pg_stats view added to CVS tip
От | Tom Lane |
---|---|
Тема | pg_stats view added to CVS tip |
Дата | |
Msg-id | 21968.992556563@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: pg_stats view added to CVS tip
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-hackers |
Per previous discussions, I have modified initdb to revoke public read access on pg_statistic (you can still read it if you're superuser, of course) and instead added a view pg_stats, which will show the statistics rows only for tables that current_user has read access to. This uses the has_table_privilege function just contributed by Joe Conway. The view also tries to be slightly more user-friendly than a raw look at pg_statistic. It joins to pg_class and pg_attribute to get the names of tables and columns, and it presents the various kinds of statistics intelligently labeled. (Thus, the view has wired into it exactly the sort of assumptions we agreed not to make in pg_statistic itself. Peter E. may be unhappy with me here. But it seems to me that we can change/extend the view definition when we add new kinds of statistics entries.) The actual definition looks like regression=# \d pg_stats View "pg_stats" Attribute | Type | Modifier -------------------+---------+----------tablename | name |attname | name |null_frac | real |avg_width | integer |n_distinct | real |most_common_vals | text[] |most_common_freqs | real[] |histogram_bounds | text[] |correlation | real | View definition: SELECT c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinctAS n_distinct, CASE WHEN (1 = s.stakind1) THEN s.stavalues1 WHEN (1 = s.stakind2) THEN s.stavalues2 WHEN (1= s.stakind3) THEN s.stavalues3 WHEN (1 = s.stakind4) THEN s.stavalues4 ELSE NULL::"_text" END AS most_common_vals, CASEWHEN (1 = s.stakind1) THEN s.stanumbers1 WHEN (1 = s.stakind2) THEN s.stanumbers2 WHEN (1 = s.stakind3) THEN s.stanumbers3WHEN (1 = s.stakind4) THEN s.stanumbers4 ELSE NULL::"_float4" END AS most_common_freqs, CASE WHEN (2 = s.stakind1)THEN s.stavalues1 WHEN (2 = s.stakind2) THEN s.stavalues2 WHEN (2 = s.stakind3) THEN s.stavalues3 WHEN (2 = s.stakind4)THEN s.stavalues4 ELSE NULL::"_text" END AS histogram_bounds, CASE WHEN (3 = s.stakind1) THEN s.stanumbers1[1]WHEN (3 = s.stakind2) THEN s.stanumbers2[1] WHEN (3 = s.stakind3) THEN s.stanumbers3[1] WHEN (3 = s.stakind4)THEN s.stanumbers4[1] ELSE NULL::float4 END AS correlat! ion FROM pg_class c, pg_attribute a, pg_statistic s WHERE ((((c.oid = s.starelid) AND (c.oid = a.attrelid)) AND (a.attnum= s.staattnum)) AND has_table_privilege(c.oid, 'select'::text)); and a sample result is: regression=# select * from pg_stats where tablename = 'tenk1' and attname = 'unique1';tablename | attname | null_frac | avg_width| n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation -----------+---------+-----------+-----------+------------+------------------+-------------------+----------------------------------------------------------------------------+-------------tenk1 | unique1 | 0 | 4 | -1 | | | {"1","966","1939","2937","3955","4994","5957","6934","8002","9044","9996"}| 0.0256536 (1 row) (See the current include/catalog/pg_statistic.h file for more info about what the columns mean.) Comments? Any suggestions on column names, layout, anything? None of this is set in stone as far as I'm concerned, it's just a first cut. regards, tom lane
В списке pgsql-hackers по дате отправления: