From: | Damian Lęcznar <damian(dot)lecznar(at)gmail(dot)com> |
---|---|
To: | pgsql-www(at)postgresql(dot)org |
Subject: | Wiki editor request |
Date: | 2018-07-18 07:50:26 |
Message-ID: | CABcGmtEWCHEsTo0i4e23Z+97jNvcMDbcmQHWeG=EHJKTbMvWWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-www |
Hey,
On page https://wiki.postgresql.org/wiki/Index_Maintenance, query for Index
size/usage statistics
Should be:
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS
table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS
index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts
AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname,
indisunique 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 *AND
psai.schemaname = 'public'*)
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;
Because if you're using as example pg_logical, and you have in your
database similar table, it will give you an error on output:
ERROR: relation "subscription_sub_name_key" does not exist
I'm using this page a lot, and I want to stop changing this query :)
Pg logical is popular right now, so if I may ask - could someone plz change
it.
Best Regards,
Damian Lęcznar
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2018-07-18 10:05:20 | Re: buildfarm server suddenly not talking to old SSL stacks? |
Previous Message | Stefan Kaltenbrunner | 2018-07-18 06:57:08 | Re: buildfarm server suddenly not talking to old SSL stacks? |