gistfile1.sql

 SELECT t.tablename, foo.indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(t.tablename::text::regclass)) AS table_size, pg_size_pretty(pg_relation_size(foo.indexrelname::text::regclass)) AS index_size, 
        CASE
            WHEN x.is_unique = 1 THEN 'Y'::text
            ELSE 'N'::text
        END AS "unique", foo.idx_scan AS number_of_scans, foo.idx_tup_read AS tuples_read, foo.idx_tup_fetch AS tuples_fetched
   FROM pg_tables t
   LEFT JOIN pg_class c ON t.tablename = c.relname
   LEFT JOIN ( SELECT pg_index.indrelid, max(pg_index.indisunique::integer) AS is_unique
      FROM pg_index
     GROUP BY pg_index.indrelid) x ON c.oid = x.indrelid
   LEFT JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, psai.idx_scan, psai.idx_tup_read, psai.idx_tup_fetch, psai.indexrelname
   FROM pg_index x
   JOIN pg_class c ON c.oid = x.indrelid
   JOIN pg_class ipg ON ipg.oid = x.indexrelid
   JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) foo ON t.tablename = foo.ctablename
  WHERE t.schemaname = 'public'::name
  ORDER BY t.tablename, foo.indexname;



-------   OUTPUT  ------------



        tablename         |            indexname            |  num_rows   | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched 
 -------------------------+---------------------------------+-------------+------------+------------+--------+-----------------+-------------+----------------
 interesting              | idx_interesting_alias           |        4704 | 2224 kB    | 136 kB     | Y      |            1012 |           2 |              2
 interesting              | interesting_pkey                |        4704 | 2224 kB    | 120 kB     | Y      |             192 |        5543 |           5543
 bigtb                    | idx_bigtb_ip_url                | 6.34879e+08 | 112 GB     | 35 GB      | Y      |          138183 |       72828 |          72744
 bigtb                    | idx_bigtb_modify_date           | 6.34879e+08 | 112 GB     | 13 GB      | Y      |             146 |        1664 |           1643
 bigtb                    | idx_bigtb_pkey                  | 6.34879e+08 | 112 GB     | 13 GB      | Y      |           87501 |       18637 |          17609
 bigtb                    | idx_bigtb_urlmd5                | 6.34879e+08 | 112 GB     | 30 GB      | Y      |               0 |           0 |              0
 bigtb_available          | bigtb_available_key             | 4.30384e+06 | 182 MB     | 92 MB      | Y      |             771 |  3317390630 |     3317390630
 bigtb_available          | bigtb_available_modify_date     | 4.30384e+06 | 182 MB     | 92 MB      | Y      |               0 |           0 |              0
 bigtb_deleted            | idxbigtb_deleted                |        3534 | 1040 kB    | 168 kB     | N      |            3075 |        2064 |           2058
 bigtb_only               |                                 |           0 | 320 kB     |            | N      |                 |             |               
 bigtb_orphans            |                                 |           0 | 0 bytes    |            | N      |                 |             |               
 bigtb_registered         | idx_bigtb_registered_pkey       | 7.68983e+06 | 1656 MB    | 169 MB     | Y      |           15895 |       25379 |          12626
 bigtb_registered         | idx_bigtb_registered_userid_url | 7.68983e+06 | 1656 MB    | 494 MB     | Y      |            1612 |       23235 |            151
 bigtb_registered_deleted |                                 |        2492 | 688 kB     |            | N      |                 |             |               
 bigtb_registered_only    |                                 |           0 | 592 kB     |            | N      |                 |             |               


Time: 22.491 ms






================ TABLE DEFINITION OF THE BIG TABLE  ===============


                              Table "public.bigtb"
     Column      |            Type             |            Modifiers            
-----------------+-----------------------------+---------------------------------
 alias           | character varying(35)       | not null
 url             | text                        | not null
 user_registered | boolean                     | 
 private_key     | character varying(6)        | default NULL::character varying
 modify_date     | timestamp without time zone | default now()
 ip              | bigint                      | 
 url_md5         | text                        | 

Indexes:
    "idx_bigtb_pkey" PRIMARY KEY, btree (alias)
    "idx_bigtb_ip_url" UNIQUE, btree (ip, url_md5)
    "idx_bigtb_modify_date" btree (modify_date)
    "idx_bigtb_urlmd5" btree (url_md5)
Check constraints:
    "bigtb_alias_check" CHECK (alias::text ~ '[-.~a-z0-9_]'::text)
Referenced by:
    TABLE "bigtb_registered" CONSTRAINT "fk_bigtb_registered" FOREIGN KEY (alias) REFERENCES bigtb(alias) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "interesting" CONSTRAINT "interesting_alias_fkey" FOREIGN KEY (alias) REFERENCES bigtb(alias) MATCH FULL ON UPDATE CASCADE
Rules:
    __track_bigtb_deleted AS
    ON DELETE TO bigtb
   WHERE NOT (EXISTS ( SELECT bigtb_deleted.alias
           FROM bigtb_deleted
          WHERE bigtb_deleted.alias::text = old.alias::text)) DO  INSERT INTO bigtb_deleted (alias, url, user_registered, modify_date) 
  VALUES (old.alias, old.url, old.user_registered, old.modify_date)





================== STATS FOR ACTIVE TABLES ===============


SELECT c.relname, c.reltuples::bigint AS rowcnt, pg_stat_get_tuples_inserted(c.oid) AS inserted, pg_stat_get_tuples_updated(c.oid) AS updated, pg_stat_get_tuples_deleted(c.oid) AS deleted
   FROM pg_class c
  WHERE c.relkind = 'r'::"char"
  GROUP BY c.oid, c.relname, c.reltuples
 HAVING (pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid)) > 1000
  ORDER BY pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid) DESC
  ;





     relname      |  rowcnt   | inserted | updated | deleted 
------------------+-----------+----------+---------+---------
 bigtb_registered |   7689826 |     1309 |   12668 |      47
 bigtb_stats      |    762639 |      885 |   12614 |      14
 pg_attribute     |      2519 |    11314 |       0 |   11304
 pg_depend        |      6801 |     5090 |       0 |    5085
 pg_class         |       325 |     1342 |    3262 |    1341
 pg_statistic     |       691 |        0 |    4579 |       0
 pg_type          |       370 |     2036 |       0 |    2034
 bigtb            | 634879168 |    65613 |       6 |    1013
(8 rows)

Time: 11.115 ms