pg_stats_ext view does not seem all that useful

Started by David Rowleyalmost 9 years ago4 messages
#1David Rowley
david.rowley@2ndquadrant.com
1 attachment(s)

During my review and time spent working on the functional dependencies
part of extended statistics I wondered what was the use for the
pg_stats_ext view. I was unsure why the length of the serialised
dependencies was useful.

Perhaps we could improve the view, but I'm not all that sure what
value it would add. Maybe we need to discuss this, but in the
meantime, I've attached a patch which just removes the view completely

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Attachments:

drop_pg_stats_ext_view.patchapplication/octet-stream; name=drop_pg_stats_ext_view.patchDownload
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index f8af42f..a4f91c7 100644
--- a/doc/src/sgml/planstats.sgml
+++ b/doc/src/sgml/planstats.sgml
@@ -520,17 +520,15 @@ EXPLAIN ANALYZE SELECT * FROM t WHERE a = 1 AND b = 1;
 
    <para>
     Similarly to per-column statistics, extended statistics are stored in
-    a system catalog called <structname>pg_statistic_ext</structname>, but
-    there is also a more convenient view <structname>pg_stats_ext</structname>.
+    a system catalog called <structname>pg_statistic_ext</structname>.
     To inspect the statistics <literal>s1</literal> defined above,
     you may do this:
 
 <programlisting>
-SELECT tablename, staname, attnums, depsbytes
-  FROM pg_stats_ext WHERE staname = 's1';
- tablename | staname | attnums | depsbytes 
------------+---------+---------+-----------
- t         | s1      | 1 2     |        40
+SELECT staname,stadependencies FROM pg_statistic_ext WHERE staname = 's1';
+ staname |              stadependencies
+---------+--------------------------------------------
+ s1      | [{1 => 2 : 1.000000}, {2 => 1 : 1.000000}]
 (1 row)
 </programlisting>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 500221a..421d51d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -186,17 +186,6 @@ CREATE OR REPLACE VIEW pg_sequences AS
     WHERE NOT pg_is_other_temp_schema(N.oid)
           AND relkind = 'S';
 
-CREATE VIEW pg_stats_ext AS
-    SELECT
-        N.nspname AS schemaname,
-        C.relname AS tablename,
-        S.staname AS staname,
-        S.stakeys AS attnums,
-        length(s.standistinct::bytea) AS ndistbytes,
-        length(S.stadependencies::bytea) AS depsbytes
-    FROM (pg_statistic_ext S JOIN pg_class C ON (C.oid = S.starelid))
-        LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace);
-
 CREATE VIEW pg_stats WITH (security_barrier) AS
     SELECT
         nspname AS schemaname,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index cba82bb..e1d0858 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2188,15 +2188,6 @@ pg_stats| SELECT n.nspname AS schemaname,
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
-pg_stats_ext| SELECT n.nspname AS schemaname,
-    c.relname AS tablename,
-    s.staname,
-    s.stakeys AS attnums,
-    length((s.standistinct)::bytea) AS ndistbytes,
-    length((s.stadependencies)::bytea) AS depsbytes
-   FROM ((pg_statistic_ext s
-     JOIN pg_class c ON ((c.oid = s.starelid)))
-     LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
 pg_tables| SELECT n.nspname AS schemaname,
     c.relname AS tablename,
     pg_get_userbyid(c.relowner) AS tableowner,
#2Tomas Vondra
tomas.vondra@2ndquadrant.com
In reply to: David Rowley (#1)
Re: pg_stats_ext view does not seem all that useful

On 04/10/2017 12:12 PM, David Rowley wrote:

During my review and time spent working on the functional dependencies
part of extended statistics I wondered what was the use for the
pg_stats_ext view. I was unsure why the length of the serialised
dependencies was useful.

Perhaps we could improve the view, but I'm not all that sure what
value it would add. Maybe we need to discuss this, but in the
meantime, I've attached a patch which just removes the view completely

Yeah, let's get rid of the view. It was quite useful before introducing
the custom data types (and implicit casts to text), because
pg_statistic_ext would simply return the whole bytea value. But since
then the view kinda lost the purpose and no one really noticed.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Tomas Vondra (#2)
Re: pg_stats_ext view does not seem all that useful

Tomas Vondra wrote:

On 04/10/2017 12:12 PM, David Rowley wrote:

During my review and time spent working on the functional dependencies
part of extended statistics I wondered what was the use for the
pg_stats_ext view. I was unsure why the length of the serialised
dependencies was useful.

Perhaps we could improve the view, but I'm not all that sure what
value it would add. Maybe we need to discuss this, but in the
meantime, I've attached a patch which just removes the view completely

Yeah, let's get rid of the view. It was quite useful before introducing the
custom data types (and implicit casts to text), because pg_statistic_ext
would simply return the whole bytea value. But since then the view kinda
lost the purpose and no one really noticed.

+1. I have other immediate commitments but I can push David's patch on
Thursday.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#4Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Tomas Vondra (#2)
Re: pg_stats_ext view does not seem all that useful

Tomas Vondra wrote:

On 04/10/2017 12:12 PM, David Rowley wrote:

During my review and time spent working on the functional dependencies
part of extended statistics I wondered what was the use for the
pg_stats_ext view. I was unsure why the length of the serialised
dependencies was useful.

Perhaps we could improve the view, but I'm not all that sure what
value it would add. Maybe we need to discuss this, but in the
meantime, I've attached a patch which just removes the view completely

Yeah, let's get rid of the view. It was quite useful before introducing the
custom data types (and implicit casts to text), because pg_statistic_ext
would simply return the whole bytea value. But since then the view kinda
lost the purpose and no one really noticed.

Thanks, pushed.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers