我可以在使用数据库后激活 PITR 吗？

Question

Mahmoud

Asked: 2022-09-19 03:25:12 +0800 CST2022-09-19 03:25:12 +0800 CST 2022-09-19 03:25:12 +0800 CST

Postgres 简单的 SQL 选择查询有时延迟会跳到 900ms

772

我有一个存储用户信息的“仅插入”表

id bigint-> 主键，不为空
user_id bigint-> 外键，不为空
名字character varying空
中间名character varying空
姓氏character varying不为空
电子邮件character varying不为空
出生日期date不为空
电话号码character varying不为空
手机号码character varying不为空
街道名称character varying空

和一个用户表（插入+更新）（大多数时候，我不查询这个表）

id主键，不为空
created_at日期，不为空
modified_at日期，空

birth_date我在表中的列上有一个索引user_info，我的查询看起来像这样

SELECT * FROM user_info WHERE birth_date = @p1 and (first_name = @p2 or email = @p3) and (last_name = @p4 or email =@p3);

大多数情况下，生产环境中的查询延迟是7-26ms但有时，而且实际上经常发生，延迟会上升，900ms所以我在日志中看到 3-4 个有延迟的查询，180, 200, 700, 900ms然后又回到7-26ms.

该表user_info是仅插入的，因此没有更新或删除
我正在使用 PostgreSQL 11
该表user_info包含 500 万条记录，数据分布在生产中看起来不错，大多数birth_date(s)在 200-1000 条记录之间，但只有一个特定的出生日期有 11 万条记录（这会是问题吗？）
服务器配置是（Azure Postgres 单服务器）中指定的默认值
服务器规格为（4 个 vCPU，20 GB 内存，第 5 代）
与生产数据库的最大并发连接数为每秒 20 个
最好的情况是只执行一个 SQL 查询（在这个问题中附加的那个）
最坏的情况是第一个：此问题中附加的 SQL 查询，第二个：将查询插入user表，第三个将查询插入user_info表
另一种情况是第一：本题附加的SQL查询，第二：向user_info表中插入查询，第三：更新user表中的修改时间
查询pg_stat_user_tables试图了解有多少顺序扫描与顺序扫描产生了以下结果：
explain analyze在我的本地机器上使用azure postgres 服务器执行两个查询pgAdmin，一个是包含最多计数的出生日期，另一个是另一个出生日期，其余值完全随机产生以下结果

我的问题是：

我需要延迟不超过某个阈值

我的问题是：

延迟跳跃背后的原因是什么user_info？
打破两个表之间的关系会解决问题吗？也许在对user表进行更新时user_info需要更新外键列，因为更新实际上是插入和删除，这会导致表中的死行？
是数据分布吗？我能做些什么来改善延迟？

更新：

我在生产中启用了 auto_explain，我使用了一个条件来仅记录延迟 > 40 毫秒的查询。并运行查询现有数据的自动化脚本，其中 4 个的出生日期为 100K 行。正如预期的那样，我只看到了该特定出生日期值的日志：

2022-09-19 {TIME REDACTED}-LOG:  duration: 42.421 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.044..42.411 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 41.370 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.087..41.359 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 41.709 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.079..41.682 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025
2022-09-19 {TIME REDACTED}--LOG:  duration: 40.581 ms  plan:
    Query Text: 
                    select * from unf.user_info unf
                    where (
                     unf.birth_date = $1
                     and ( unf.first_name = $2 or unf.email = $3 )
                     and ( unf.last_name = $4 or unf.email = $3 ))
    Index Scan using idx_b_date on unf.user_info unf  (cost=0.43..76483.96 rows=1 width=112) (actual time=0.057..40.568 rows=2 loops=1)
      Output: id, user_id, birth_date, first_name, last_name, email,  phone_number, mobile_number, street_name, created_date
      Index Cond: (unf.birth_date = '{REDACTED} 00:00:00'::timestamp without time zone)
      Filter: ((((unf.last_name)::text = '{REDACTED}'::text) OR ((unf.email)::text = '{REDACTED}'::text)) AND (((unf.first_name)::text = 'n'::text) OR ((unf.email)::text = '{REDACTED}'::text)))
      Rows Removed by Filter: 119228
      Buffers: shared hit=11025

2 个回答

Voted

ypercubeᵀᴹ · Answer 1 · 2022-09-20T03:47:46+08:00

查询的索引条件是不必要的复杂：

-- version 1, original
WHERE birth_date = @p1 
  AND ( first_name = @p2 OR email = @p3 )
  AND ( last_name = @p4 OR email = @p3 )

它相当于：

-- version 2
WHERE birth_date = @p1 
  AND (  first_name = @p2 AND last_name = @p4
      OR email = @p3
      )

对此（括号是多余的，仅为了清楚起见）：

-- version 3
WHERE ( birth_date = @p1 AND first_name = @p2 AND last_name = @p4 )
   OR ( birth_date = @p1 AND email = @p3 )

上面的第 3 个变体使识别更好的索引候选变得更容易，无论是对于人类还是 Postgres 的规划器。规划器具有一些重写/简化布尔条件的能力，但它不能识别所有可能的简化。

因此，我建议您添加这两个索引：

INDEX (birth_date, first_name, last_name)
INDEX (birth_date, email)

并检查上面的第三个选项和重写使用UNION：

-- version 3
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND first_name = @p2 AND last_name = @p4
   OR birth_date = @p1 AND email = @p ;

-- version 4: UNION
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND first_name = @p2 AND last_name = @p4
UNION
SELECT * 
FROM user_info 
WHERE birth_date = @p1 AND email = @p3  ;

Laurenz Albe · Answer 2 · 2022-09-20T03:05:12+08:00

Best Answer

Laurenz Albe

2022-09-20T03:05:12+08:002022-09-20T03:05:12+08:00

在您的慢查询中，有 1970 行birth_date = 'REDACTED'，而在慢查询中，有 124287 行。所以很自然，索引扫描需要更长的时间。

由于您将 aOR放入您的WHERE条件（这使得索引更难），我能想到的唯一简单改进是将过滤器列添加到索引中：

CREATE INDEX ON user_info (birth_date, first_name, email);

也许您可以重写查询以避免OR，那么更多是可能的。

1

Postgres 简单的 SQL 选择查询有时延迟会跳到 900ms

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

Postgres 简单的 SQL 选择查询有时延迟会跳到 900ms

2 个回答

相关问题