主页 / user-141921

Mahmoud's questions

Martin Hope
Mahmoud
Asked: 2022-09-19 03:25:12 +0800 CST
  • -1

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

  • 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
postgresql postgresql-performance