Commitd29ff58

committed

Fix index bloat metrics for user schemas by replacing pg_stats with public.pg_statistic view

1 parentd929077 commitd29ff58Copy full SHA for d29ff58

File tree

+30

-16

lines changed

+30

-16

lines changed

Lines changed: 13 additions & 6 deletions

Original file line number	Diff line number	Diff line change
`@@ -20,13 +20,20 @@ grant select on pg_stat_statements to postgres_ai_mon;`
`20`	`20`	`grantselecton pg_stat_database to postgres_ai_mon;`
`21`	`21`	`grantselecton pg_stat_user_tables to postgres_ai_mon;`
`22`	`22`
`23`		`--- Create a public view for pg_statistic access for bloat metrics`
	`23`	`+-- Create a public view for pg_statistic access(requiredfor bloat metrics on user schemas)`
`24`	`24`	`CREATEVIEWpublic.pg_statisticAS`
`25`		`-SELECTpg_statistic.stawidth,`
`26`		`-pg_statistic.stanullfrac,`
`27`		`-pg_statistic.starelid,`
`28`		`-pg_statistic.staattnum`
`29`		`-FROM pg_statistic;`
	`25`	`+SELECT`
	`26`	`+n.nspnameas schemaname,`
	`27`	`+c.relnameas tablename,`
	`28`	`+a.attname,`
	`29`	`+s.stanullfracas null_frac,`
	`30`	`+s.stawidthas avg_width,`
	`31`	`+ falseas inherited`
	`32`	`+FROM pg_statistic s`
	`33`	`+JOIN pg_class cONc.oid=s.starelid`
	`34`	`+JOIN pg_namespace nONn.oid=c.relnamespace`
	`35`	`+JOIN pg_attribute aONa.attrelid=s.starelidANDa.attnum=s.staattnum`
	`36`	`+WHEREa.attnum>0AND NOTa.attisdropped;`
`30`	`37`
`31`	`38`	`GRANTSELECTONpublic.pg_statistic TO pg_monitor;`
`32`	`39`	`ALTERUSER postgres_ai_monset search_path="$user", public, pg_catalog;`

Lines changed: 4 additions & 4 deletions

Original file line number	Diff line number	Diff line change
`@@ -1512,9 +1512,9 @@ metrics:`
`1512`	`1512`	`and a2.attnum = ic.attpos`
`1513`	`1513`	`) i`
`1514`	`1514`	`join pg_catalog.pg_namespace n on n.oid = i.relnamespace`
`1515`		`- joinpg_catalog.pg_stats s on s.schemaname = n.nspname`
`1516`		`-and s.tablename = i.attrelname`
`1517`		`-and s.attname = i.attname`
	`1515`	`+joinpublic.pg_statistic s on s.schemaname = n.nspname`
	`1516`	`+ and s.tablename = i.attrelname`
	`1517`	`+ and s.attname = i.attname`
`1518`	`1518`	`group by 1,2,3,4,5,6,7,8,9,10,11`
`1519`	`1519`	`) as rows_data_stats`
`1520`	`1520`	`) as rows_hdr_pdg_stats`
`@@ -1583,7 +1583,7 @@ metrics:`
`1583`	`1583`	`from pg_attribute as att`
`1584`	`1584`	`join pg_class as tbl on att.attrelid = tbl.oid`
`1585`	`1585`	`join pg_namespace as ns on ns.oid = tbl.relnamespace`
`1586`		`- left joinpg_stats as s on s.schemaname=ns.nspname`
	`1586`	`+ left joinpublic.pg_statistic as s on s.schemaname=ns.nspname`
`1587`	`1587`	`and s.tablename = tbl.relname and s.inherited=false and s.attname=att.attname`
`1588`	`1588`	`left join pg_class as toast on tbl.reltoastrelid = toast.oid`
`1589`	`1589`	`where not att.attisdropped`

Lines changed: 13 additions & 6 deletions

Original file line number	Diff line number	Diff line change
`@@ -21,12 +21,19 @@ GRANT CONNECT ON DATABASE target_database TO monitor;`
`21`	`21`	`GRANT USAGEON SCHEMA public TO monitor;`
`22`	`22`
`23`	`23`	`-- Create a public view for pg_statistic access`
`24`		`-CREATEVIEWpublic.pg_statisticAS`
`25`		`-SELECTpg_statistic.stawidth,`
`26`		`-pg_statistic.stanullfrac,`
`27`		`-pg_statistic.starelid,`
`28`		`-pg_statistic.staattnum`
`29`		`-FROM pg_statistic;`
	`24`	`+CREATE OR REPLACEVIEWpublic.pg_statisticAS`
	`25`	`+SELECT`
	`26`	`+n.nspnameas schemaname,`
	`27`	`+c.relnameas tablename,`
	`28`	`+a.attname,`
	`29`	`+s.stanullfracas null_frac,`
	`30`	`+s.stawidthas avg_width,`
	`31`	`+ falseas inherited`
	`32`	`+FROM pg_statistic s`
	`33`	`+JOIN pg_class cONc.oid=s.starelid`
	`34`	`+JOIN pg_namespace nONn.oid=c.relnamespace`
	`35`	`+JOIN pg_attribute aONa.attrelid=s.starelidANDa.attnum=s.staattnum`
	`36`	`+WHEREa.attnum>0AND NOTa.attisdropped;`
`30`	`37`
`31`	`38`	`-- Grant specific access instead of all tables`
`32`	`39`	`GRANTSELECTONpublic.pg_statistic TO pg_monitor;`

Comments

(0)