时间戳

首页 > 解决方案 > Postgresql 查询运行缓慢和快速。解释计划显示高共享读取

postgresql - Postgresql 查询运行缓慢和快速。解释计划显示高共享读取

问题描述

我有一个高吞吐量表，每天大约有 2000 万次插入，其中 N_PROCESSING_STATE = 0 一组进程从该表中选择新记录，将它们分组并插入或更新另一个表。完成后，处理过的记录将更新为 N_PROCESSING_STATE = 1 每日内务工作会删除这些记录。

我的问题是 select 有时运行得很快，有时运行得很慢。我已经解释了在 20 分钟内多次运行相同查询的计划结果，需要帮助理解为什么速度如此不同

这里举三个例子。上一个完成后几乎直接运行。

Limit  (cost=0.56..6541.54 rows=165 width=6155) (actual time=0.088..137855.110 rows=990 loops=1)
  Buffers: shared hit=6026143 read=432018
  ->  Index Scan using ipc_message_print_proc_state on ipc_message_print  (cost=0.56..6541.54 rows=165 width=6155) (actual time=0.086..137854.983 rows=990 loops=1)
        Index Cond: (n_processing_state = 0)
        Filter: (mod((ascii(substr((c_transaction_id)::text, 1, 1)) + ascii("right"((c_transaction_id)::text, 1))), 4) = 3)
        Rows Removed by Filter: 3111
        Buffers: shared hit=6026143 read=432018
Planning Time: 0.499 ms
Execution Time: 137855.332 ms

Limit  (cost=0.56..6546.66 rows=165 width=6155) (actual time=0.063..27.692 rows=3000 loops=1)
  Buffers: shared hit=9232 read=2
  ->  Index Scan using ipc_message_print_proc_state on ipc_message_print  (cost=0.56..6546.66 rows=165 width=6155) (actual time=0.061..27.346 rows=3000 loops=1)
        Index Cond: (n_processing_state = 0)
        Filter: (mod((ascii(substr((c_transaction_id)::text, 1, 1)) + ascii("right"((c_transaction_id)::text, 1))), 4) = 3)
        Rows Removed by Filter: 8869
        Buffers: shared hit=9232 read=2
Planning Time: 0.451 ms
Execution Time: 27.992 ms

Limit  (cost=0.56..11645.97 rows=289 width=6157) (actual time=0.064..141655.565 rows=973 loops=1)
  Buffers: shared hit=6194738 read=444040 written=938
  ->  Index Scan using ipc_message_print_proc_state on ipc_message_print  (cost=0.56..11645.97 rows=289 width=6157) (actual time=0.062..141655.472 rows=973 loops=1)
        Index Cond: (n_processing_state = 0)
        Filter: (mod((ascii(substr((c_transaction_id)::text, 1, 1)) + ascii("right"((c_transaction_id)::text, 1))), 4) = 3)
        Rows Removed by Filter: 3127
        Buffers: shared hit=6194738 read=444040 written=938
Planning Time: 5.542 ms
Execution Time: 141655.720 ms

我可以看到“快速”仅使用缓存数据。我还看到其他人正在阅读新条目。我不明白为什么会有巨大的差异。最快的是检索 3000，因为有一个限制集，我认为这会有所帮助。问题是为什么需要为其他两个查询读取超过 400,000 条记录。为什么他们在第一次尝试后没有被缓存。在此期间插入的新记录肯定少于 200,000 条。

如果我需要提供更多内存，我可以在某个地方看到吗？（共享缓冲区设置为 24GB）

我的实际查询

explain (analyze,buffers,timing) SELECT K_MESSAGE_PRINT_ID, D_PRINT_TIMESTAMP, C_MESSAGE_ID, C_TRANSACTION_ID, C_MESSAGE_PRINT_TYPE, N_MESSAGE_STATE, B_MESSAGE_ACTIVE
, FK_INFRA_OBJECT_ID, FK_FLOW_STEP_ID, FK_NEXT_FLOW_STEP_ID, FK_MESSAGE_CATEGORY, FK_ACK_USER_PROFILE_ID, FK_SERVICE_FORMAT_ID, FK_MESSAGE_PROFILE_ID
, FK_MESSAGE_TYPE_VERSION_ID, FK_MESSAGE_INSTANCE_ID, FK_WORKFLOW_ID, OS_WORKFLOW_ACTION_ID, C_REF_1, C_REF_2, C_REF_3, C_VISIBILITY_CODE
, FK_VISIBILITY_USER_PROFILE_ID, FK_VISIBILITY_GROUP_ID, FK_VISIBILITY_ORGANISATION_ID, C_TAG_INFO
, CASE WHEN CLOB_MESSAGE IS NULL THEN 'no' ELSE 'yes' END AS HAS_CLOB_MESSAGE, CASE WHEN CLOB_PROPRIETARY_MESSAGE IS NULL THEN 'no' ELSE 'yes' END AS HAS_PROPRIETARY_CLOB_MESSAGE
, CASE WHEN CLOB_MESSAGE_ERRORS IS NULL THEN 'no' ELSE 'yes' END AS HAS_CLOB_MESSAGE_ERRORS, CASE WHEN CLOB_STATUS_MSG IS NULL THEN 'no' ELSE 'yes' END AS HAS_CLOB_STATUS_MESSAGE
, C_STATUS_CODE, C_COMMENT, C_TARGET_INFO, N_PROCESSING_STATE, C_LINK_INFO, C_GATE_NAME,C_MESSAGE_SUB_STATE, C_MESSAGE_TYPE, C_MESSAGE_TYPE_2
, C_ORIGINAL_SENDER, C_FINAL_RECEIVER, C_SENDER, C_RECEIVER, C_MESSAGE_ID_2, C_MESSAGE_REF, D_VALUE_DATE, C_AMOUNT, C_AMOUNT_CURR, C_ORGANISATION
, C_ORGANISATION_2, N_AMOUNT_VALUE,C_ATT_21, C_ATT_23, C_ATT_22, C_ATT_24, C_ATT_7, C_ATT_3, C_ATT_11, C_ATT_25, C_ATT_1, C_ATT_19, C_ATT_4
, C_ATT_5, C_ATT_13, C_ATT_9, C_ATT_2, C_ATT_10, C_ATT_20, C_ATT_18, C_ATT_26, C_ATT_15, C_ATT_12, C_ATT_6, C_ATT_8, C_ATT_14, N_ATT_2, N_ATT_4
, N_ATT_13, N_ATT_14, N_ATT_1, N_ATT_6, N_ATT_12, N_ATT_3, N_ATT_11, D_ATT_3, D_ATT_1, D_ATT_2, D_ATT_4, D_ATT_5, D_ATT_6 
FROM IPC_MESSAGE_PRINT 
WHERE N_PROCESSING_STATE = 3 
AND MOD(ASCII(SUBSTR(C_TRANSACTION_ID,1,1)) + ASCII(RIGHT(C_TRANSACTION_ID, 1)),4) = 0  
limit 3000

我每 15 分钟在这张桌子上设置一次真空分析，这让情况变得更好。我还运行了一个完整的真空来验证没有膨胀问题并且索引很好。

标签： postgresqlexplain

解决方案

您有长期未结交易吗？

PostgreSQL 不在索引中存储可见性信息。因此，没有找到 3000 个条目的查询必须遍历 n_processing_state = 0 的整个索引部分，然后转到表行，才发现元组不再可见（它已被更新，因此 n_processing_state 不更长的 0，或已被删除）。这很慢。如果它发现元组对自身或任何其他现有事务不再可见，那么它将在索引中将其标记为已死，因此下一个查询不必重复此操作。但是，如果有任何其他事务可能希望查看该元组，则它不能在索引中将其标记为已死。因此，一项被遗忘的事务可能会导致其他人一遍又一遍地访问相同的过时行。

问题是为什么需要为其他两个查询读取超过 400,000 条记录。

那是 400,000 页，而不是记录。EXPLAIN 中没有指示访问了多少行但发现不可见。据推测，它远远超过 400,000。

完成后，处理过的记录将更新为 N_PROCESSING_STATE = 1 每日内务工作会删除这些记录。

您是否有理由不能立即删除它们？UPDATE 后不久 DELETE 将创建更多需要处理的死行。

推荐阅读