TMP
1. qrw_inlist2join_optmode='disable'; 一定没有该问题
2. qrw_inlist2join_optmode='cost_base' inlist长度<10; 一定没有该问题
3. qrw_inlist2join_optmode='cost_base' inlist长度>=10; 可能有该问题
4. qrw_inlist2join_optmode='rule_base'; 可能有该问题
CREATE TABLE emp (
empno INTEGER PRIMARY KEY,
ename TEXT
);
INSERT INTO emp (empno, ename) VALUES
(7369, 'SMITH'),
(7499, 'ALLEN'),
(7521, 'WARD'),
(7566, 'JONES'),
(7698, 'BLAKE');
CREATE OR REPLACE FUNCTION get_emp_rows()
RETURNS SETOF my_obj_type AS $$
DECLARE
result my_obj_type;
BEGIN
FOR rec IN SELECT empno, ename FROM emp LOOP
result := (rec.empno, rec.ename);
RETURN next result;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_emp_rows();
-- 按照时间倒排慢SQL
\x
SELECT unique_query_id, max(db_time), count(1)
FROM statement_history where start_time >= '2025-06-06 00:00:00' and start_time < '2025-06-22 00:00:00' order by 2 desc;
-- 按照执行频率排序慢SQL
\x
SELECT unique_query_id, max(db_time), count(1)
FROM statement_history where start_time >= '2025-04-21 00:00:00' and start_time < '2025-04-22 00:00:00' order by 3 desc;
-- 每条SQL提取最大时间的记录
select distinct on(unique_query_id)statement_detail_decode(details,'plaintext',true),* from statement_history where start_time >= '2025-04-21 00:00:00' and start_time < '2025-04-22 00:00:00' order by unique_query_id, db_time desc;
数据截面备份(汇总库)
gs_dump -h 215.79.12.113 -U lp_ipps_db -p 8000 -W ipps_123 ipps_all_9_1 -F c -a -f /data/backup/databack/ipps_all_20250000_9_1.dump > /data/backup/databack/ipps_all_20250000.log 2>&1 &