本期内容如下：
- 查看索引字段的排序属性
- 快捷查看参数生效方式
- commit慢排查方向
- 外部表优化
1.索引字段的排序属性如何查询
在PG里创建索引时可以指定字段按默认的asc升序或desc降序存储，还包括对NULL的排序，示例如下：
CREATE INDEX ON tab(id DESC NULLS LAST);
索引字段的排序属性并未直接记录到相关的系统字典表，但可以通过pg_get_indexdef函数查看：
postgres=# SELECT pg_get_indexdef('tab_id_idx'::regclass);
pg_get_indexdef
------------------------------------------------------------------------
CREATE INDEX tab_id_idx ON public.tab USING btree (id DESC NULLS LAST)
(1 row)
2.快捷查看参数生效方式
比如源码编译方式安装完数据库后，logging_collector默认是关闭的，开启该参数后需要重启数据库。而更改日志记录级别log_statement以及保留时间等，是否也需要重启数据库呢？
使用15及以上版本的psql可以非常快捷的通过"\dconfig+"查看：
postgres=# \dconfig+ logging_collector
List of configuration parameters
Parameter | Value | Type | Context | Access privileges
-------------------+-------+------+------------+-------------------
logging_collector | on | bool | postmaster |
(1 row)
postgres=# \dconfig+ log_statement
List of configuration parameters
Parameter | Value | Type | Context | Access privileges
---------------+-------+------+-----------+-------------------
log_statement | mod | enum | superuser |
(1 row)
3.commit慢点排查方向
commit提交事务是一件很轻量的事儿，它仅需注册clog文件里2个比特位的状态，也无需对数据表进行操作，通常很快。
如果活动会话视图监控到commit操作产生等待，优先排查的方向是操作系统层I/O是否存在压力，检查I/O是否使用NAS，TCP网络是否超载，或者共享存储是否有争用，磁盘或者操作系统硬件相关的问题。
数据库层的排查因素：
- 延迟约束和触发器
- 游标使用WITH HOLD跨事务物化结果集
- 同步流复制是否正常
- 异步通知NOTIFY
- 第三方FDW扩展插件
4.外部表优化
postgres_fdw使用过程中大家可能会遇到一些性能问题，下面关于postgres_fdw的一些特性可以参考：
-
支持的下推操作：agg聚合、where条件、order by排序、limit分页、join表、case表达式
-
并行能力：async_capable(ForeignScan)、parallel_commit、parallel_abort
-
成本预估：use_remote_estimate、fdw_startup_cost、fdw_tuple_cost、analyze_sampling
-
缓存设置：fetch_size、batch_size
-
Remote端GUC设置：比如设置work_mem参数
下面这篇文章记录了外部表的一些优化技巧：
https://www.crunchydata.com/blog/performance-tips-for-postgres-fdw
1）使用with语句，避免本地过滤产生大量fetch
WITH filtered_remote_table AS (
SELECT * FROM remote_table WHERE created_at > now() - '1 day'::interval
)
SELECT *
FROM local_table
LEFT JOIN filtered_remote_table
ON local_table.id = filtered_remote_table.local_table_id
2）子查询嵌套在in里面，所有的数据行都需要从远端返回，使用any进行优化。
WITH filtered_local_table AS (
SELECT
local_table.id,
local_table.name
FROM local_table
WHERE local_table.a_column = 'some_value'
), filtered_remote_table AS (
SELECT
remote_table.l_id,
remote_table.created_at
FROM remote_table
WHERE remote_table.l_id = ANY ((SELECT ARRAY_AGG(DISTINCT filtered_local_table.id) FROM filtered_local_table)::integer[])
)
SELECT
filtered_local_table.name,
COUNT(DISTINCT filtered_remote_table.created_at)
FROM filtered_local_table
INNER JOIN filtered_remote_table ON filtered_local_table.id = filtered_remote_table.l_id
GROUP BY 1
ORDER BY 2 DESC;
3）设置fetch_size
ALTER SERVER remote_server OPTIONS (fetch_size '10000');
4）使用物化视图同步数据，并对修改的数据使用merge语句进行合并
MERGE INTO cached_remote_table
USING (
WITH last_sync AS (
SELECT max(GREATEST(updated_at, created_at)) AS last_at
FROM cached_remote_table
)
SELECT
received_at,
customer_id,
action
FROM remote_table
WHERE remote_table.received_at >= COALESCE((SELECT last_at FROM last_sync), '0001-01-01'::timestamp)
OR remote_table.updated_at >= COALESCE((SELECT last_at FROM last_sync), '0001-01-01'::timestamp)
) AS remote_table_to_sync
ON cached_remote_table.id = remote_table_to_sync.id
WHEN NOT MATCHED THEN
INSERT VALUES (remote_table_to_sync.a_column, remote_table_to_sync.created_at, remote_table_to_sync.updated_at)
WHEN MATCHED THEN
UPDATE SET a_column = remote_table_to_sync.a_column, updated_at = remote_table_to_sync.updated_at
;
推荐阅读
与我联系
- 微信公众号：象楚之行
- 墨天轮：https://www.modb.pro/u/15675
- 微信：skypkmoon
勤耕细作，用心积微；静待花开，量变质成。
