PostgreSQL常用SQL

分区表主表与子表一致性检查

1.分区表检查sql

SELECT parent.relname AS parent,max(child.relname) AS child
FROM pg_inherits  JOIN pg_class parent ON pg_inherits.inhparent = parent.oid  JOIN pg_class child ON pg_inherits.inhrelid = child.oid
--WHERE parent.relname like 't_%'
group by parent.relname
order by parent.relname;

分区表下有多少分区子表分区表下有多少分区子表

SELECT parent.relname AS parent,child.relname AS child
FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid
order by 1,2;

select relname , consrc  from pg_inherits i join pg_class c on c.oid = inhrelid join pg_constraint on c.oid = conrelid where contype = 'c' and inhparent in (SELECT distinct parent.oid AS parent FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid) and (relname like '%201812' or relname like '%201901') order by relname asc;

2.分区表索引检查

CREATE OR REPLACE FUNCTION check_partition_table_index(
in_benchmark_table varchar,
in_parent_table varchar
)
RETURNS void AS $$
DECLARE
v_sql_str text;
cur_all_child_table refcursor;
rec_all_child_table record;
v_table_name varchar;
cur_check_result refcursor;
rec_check_result record;
v_create_index_str text;
BEGIN
v_sql_str := ' SELECT child.relname AS table_name
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = $1
AND child.relname <> $2
order by parent.relname';

open cur_all_child_table for execute v_sql_str using in_parent_table, in_benchmark_table;
loop
fetch cur_all_child_table INTO rec_all_child_table;
if not found then
exit;
end if;
v_table_name := rec_all_child_table.table_name;

v_sql_str := 'select pg_get_indexdef(i.indexrelid) as create_index_str
from pg_index i
where indrelid = (select oid from pg_class where relname = $1)
and not exists
(select 1 from pg_index pi
where pi.indrelid = (select oid from pg_class where relname = $2)
and pi.indnatts = i.indnatts
and pi.indisunique = i.indisunique
and pi.indkey = i.indkey
and pi.indcollation = i.indcollation
and pi.indclass = i.indclass
and pi.indoption = i.indoption
)';
--基准表的索引是否都在其他表中
open cur_check_result for execute v_sql_str using in_benchmark_table, v_table_name;
loop
fetch cur_check_result INTO rec_check_result;
if not found then
exit;
end if;
v_create_index_str := rec_check_result.create_index_str;
raise warning '% lacks %', v_table_name, v_create_index_str;
end loop;
close cur_check_result;

--其他表的索引是否都在基准表中
open cur_check_result for execute v_sql_str using v_table_name, in_benchmark_table;
loop
fetch cur_check_result INTO rec_check_result;
if not found then
exit;
end if;
v_create_index_str := rec_check_result.create_index_str;
raise warning '% adds %', v_table_name, v_create_index_str;
end loop;
close cur_check_result;
end loop;
close cur_all_child_table;
END;
$$ LANGUAGE plpgsql;

SELECT 'select check_partition_table_index('''||child.relname||''','''||parent.relname||''');'
FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE child.relname like '%201812'
order by parent.relname;

pg数据库sql awr

select pg_stat_statements_reset();

cpu排序

select *, total_time/calls as per_time, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by total_time desc limit 10) as a;

select substr(query,0,100), calls, total_time,sum_time, total_time/calls as per_time, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by total_time desc limit 10) as a;

读排序

select *, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5) as a;

calls排序

select *,calls, total_time/calls as per_time, round(calls*100/sum_calls) as ratio from (
select query, calls, total_time, (select sum(calls) from public.pg_stat_statements) as sum_calls from public.pg_stat_statements order by calls desc limit 10) as a;

per_time排序

select *,calls, total_time/calls as per_time, round(calls*100/sum_calls) as ratio from (
select query, calls, total_time, (select sum(calls) from public.pg_stat_statements) as sum_calls from public.pg_stat_statements where query not like '%__rds_pg_stats__%') as a order by per_time desc limit 10;