Thread: Wiki editor request
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,
On 2018-Jul-18, Damian Lęcznar wrote: > Hey, > > On page https://wiki.postgresql.org/wiki/Index_Maintenance, query for Index > size/usage statistics > Should be: So what's your community username? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hey,
Damianl - because I used google for login :/
Best Regards,
Damian Lęcznar
czw., 26 lip 2018 o 22:50 Alvaro Herrera <alvherre@2ndquadrant.com> napisał(a):
On 2018-Jul-18, Damian Lęcznar wrote:
> Hey,
>
> On page https://wiki.postgresql.org/wiki/Index_Maintenance, query for Index
> size/usage statistics
> Should be:
So what's your community username?
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-Jul-26, Damian Lęcznar wrote: > Hey, > > Damianl - because I used google for login :/ Sounds good enough :-) OK, you can edit now. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Great, thanks!
czw., 26 lip 2018 o 23:19 Alvaro Herrera <alvherre@2ndquadrant.com> napisał(a):
On 2018-Jul-26, Damian Lęcznar wrote:
> Hey,
>
> Damianl - because I used google for login :/
Sounds good enough :-)
OK, you can edit now.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services