问题[postgresql-performance](dba)

我在不使用索引的键上有一个大型表连接。

在这里查询和计划： https://explain.dalibo.com/plan/15a6a91492g8g935#plan/node/5

两个表在键上都有一个索引，如下所示：

CREATE UNIQUE INDEX "stage_fact_revenue_900_idx_a" ON "stage"."stage_fact_revenue_900_add_dims" USING BTREE ("date","billed_relation_id","subject_client_id","identifier","revenue_type");
CREATE UNIQUE INDEX "fact_revenue_idx_a" ON "star"."fact_revenue" USING BTREE ("date","identifier","billed_relation_id","subject_client_id","revenue_type");

但未使用索引（请参阅查询计划）。

有任何线索说明为什么这种情况没有发生吗？最大的步骤是排序，我可以对表进行预排序来防止这种情况发生吗？

CREATE OR REPLACE FUNCTION public.fn_question_3_3()
RETURNS TABLE(name character varying, surname character varying, person_t person_type)
AS
$$
DECLARE
       pt person_type;
BEGIN
     RETURN QUERY
     SELECT DISTINCT pr.name, pr.surname, pr.person_t
     FROM "Person" pr
     CROSS JOIN "Professor" prof 
     CROSS JOIN "LabTeacher" lt 
     CROSS JOIN "Student" st;
           IF pr.amka = prof.amka THEN
                pt = 'Professor';
           ELSIF pr.amka = lt.amka THEN
                pt = 'LabTeacher';
           ELSE
                pt = 'Student';
           END IF;
           pt = pr.person_t;
END;
$$

LANGUAGE 'plpgsql';

SELECT*FROM fn_question_3_3()

所以我试图在这里做一个简单的查询，我检查这个人的 amka，如果它与其他表之一相同，那么它更新 person_t 列并返回姓名、姓氏和 person_t。

问题是查询SELECT * FROM fn_wuestion_3_3()需要超过 10 分钟才能完成，而对于这样的查询，情况不应该如此。任何帮助将不胜感激，因为我对数据库和 postgresql/pgadmin4 很陌生。

表：“Person”有 4820 行
表：“Professor”有 123 行
表：“LabTeacher”有 77 行
表：“Student”有 3194 行

索引定义分别为“Person”的 pr、“Professor”的 prof、“Student”的 st 和“LabTeacher”表的 lt。

我正在努力为我的数据结构优化正确的索引创建。我正在尝试索引大量 json 数据类型的数据（~100B 记录）：

{
  "email": "[email protected]",
  "first_domain": "q.w.e.r.t.y.com"
  "second_domain": "a.s.d.f.g.h.j.com"
}

简单的查询可能看起来像

SELECT * 
from table 
WHERE email = '[email protected]'

或者

SELECT * 
from table 
WHERE first_domain LIKE '%.r.t.y.com'
   OR second_domain LIKE '%.r.t.y.com'

以前我会将所有域标记化并将它们放在 mongo 中的数组中

{
  "values": ["[email protected]", "moc.y.t.r.e.w.q", moc.j.h.g.f.d.s.a]
}

但是拥有 amount I 数据的 Mongo 无法将所有值存储在内存中并且写入性能会降低。

所以我打算试试 Postgres 和 GIN 索引。请建议我为这种情况创建适当的索引。

我有一个脚本，它运行大约 60k 更新查询，其中包含近 500 万条记录的表上的 where 子句。更新查询很可能会更新每个案例的所有行。有没有更好的方法来优化它，因为它目前已经运行了几个小时？（myTable 中的 where 子句列没有索引，但它确实有主键）

update myTable set Col1 = Val1 where Col1 = Unq1
update myTable set Col2 = Val4 where Col2 = Unq23
update myTable set Col3 = Val8 where Col3 = Unq45
.......

我有 10-15 个大表，5GB 到 30GB。其余的不到 1GB。一些表数据很大，每天会产生 500 000 个死元组。几乎 7 天后，当死元组超过 350 万时，autovacuum 开始。这个数据库有大约 60 个表。这里数据库的阈值为：20%。

我想将 autovacuum 阈值从 20% 更改为 1%。是否对系统性能有任何影响（因为对于较小的表，它会经常运行）。maintanance_work_mem 和 work_mem 参数值应该是什么？

我有一张ticketing_system桌子，我尝试插入 10+ 百万行。所以这需要 50 秒。但我最多需要 10-15 秒。

CREATE TABLE ticketing_system (
  ticket_id UUID not null default uuid_generate_v4(),
  count int,
);

这是一个表结构。这是一个表结构。我只是使用这个查询。

INSERT INTO ticketing_system(count) 
select * from generate_series(1,10000000);

此查询在将近 50 秒内插入数据。

explain analyze INSERT INTO ticketing_system(count) select * from generate_series(1,10000000);
                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Insert on ticketing_system  (cost=0.00..125000.00 rows=0 width=0) (actual time=52620.980..52620.981 rows=0 loops=1)
   ->  Function Scan on generate_series  (cost=0.00..125000.00 rows=10000000 width=20) (actual time=765.900..44678.638 rows=10000000 loops=1)
 Planning Time: 0.056 ms
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions authentic, Deforming true
   Timing: Generation 0.578 ms, Inlining 0.000 ms, Optimization 0.174 ms, Emission 1.199 ms, Total 1.952 ms
 Execution Time: 52631.718 ms
(8 rows)

如何优化这个查询？我删除了索引。

我在工作中遇到了一点问题，ORM 正在生成一个非常奇怪的查询，我需要稍微优化它的结果。我写了一个更具可读性的查询版本，结果相似，但执行时间仍然相同。

原始查询
解释详细

这是当前格式化的查询，它返回正确的值，但执行大约需要 13 秒。

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid AND
                       bi.propertyid = '84'
                       AND NOT bi.isdeleted
                       AND bi.groupkey = bi.billingaccountid
                       AND bi.billingaccounttypeid = '3'
                       AND bi.reservationid IS NOT NULL
                       AND bi.statusid = '1')
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

解释详细

这是我写的尝试优化的版本（从13秒到5秒），避开了left join里面的条件，但是结果和原来的查询不一样。第一个查询返回 29490，第二个查询返回 29397。

select count(*)
from reservation as r
         JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
         JOIN reservationitem as ri ON r.reservationid = ri.reservationid
         LEFT JOIN billingaccount as bi
                   ON (r.reservationid = bi.reservationid AND cc.companyclientid = bi.companyclientid)
WHERE r.propertyid = '84'
  AND NOT r.isdeleted
  AND r.companyclientid is not null
  AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
  AND NOT ri.isdeleted
  AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8)
  AND (bi is null or bi.propertyid = '84'
  AND NOT bi.isdeleted
  AND bi.groupkey = bi.billingaccountid
  AND bi.billingaccounttypeid = '3'
  AND bi.reservationid IS NOT NULL
  AND bi.statusid = '1')

解释详细

我的问题是，如何优化第一个查询，我尝试了一些方法，但没有取得多大成功。我知道计数是线性的，它的时间基于查询返回的大小，但我认为对于 30K 行查询来说太慢了。

在这种特定情况下，我需要项目总数来计算限制偏移分页中的页数。

提前感谢所有耐心阅读的人，我接受任何帮助。

示例中使用的表大小：

• 预留：288549 行
• companyclient: 50614行
• 预留项：387820 行
• 计费帐户：772521 行

所以这是我第一次接触大数据。我有一张ticketing_system表，我在表中插入了一百万（十亿）个假数据。

CREATE TABLE ticketing_system (
    id BIGSERIAL,
    ticket_id UUID not null default uuid_generate_v4(),
    count int,
    created_at timestamptz NOT NULL
);

ticket_id搜索with子句时，我需要大约 10-30 毫秒的 Postgresql 响应。我where创建了 BRIN 索引 CREATE INDEX in_ticketing_system_brin ON ticketing_system USING brin(ticket_id);。但这对我没有帮助。

explain analyze select * from ticketing_system where ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99';
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1217.39..1854198.38 rows=1 width=36) (actual time=997.056..84730.718 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Bitmap Heap Scan on ticketing_system  (cost=217.39..1853198.28 rows=1 width=36) (actual time=56707.851..84601.029 rows=0 loops=3)
         Recheck Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
         Rows Removed by Index Recheck: 46103039
         Heap Blocks: lossy=366097
         ->  Bitmap Index Scan on in_ticketing_system_brin  (cost=0.00..217.39 rows=3544816 width=0) (actual time=153.203..153.209 rows=11525760 loops=1)
               Index Cond: (ticket_id = '09830cb7-37f2-4951-8910-1661b1358b99'::uuid)
 Planning Time: 3.167 ms
 JIT:
   Functions: 6
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 12.892 ms, Inlining 491.333 ms, Optimization 92.106 ms, Emission 91.551 ms, Total 687.882 ms
 Execution Time: 84833.538 ms
(15 rows)

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

• 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

我在一个托管多个大型数据库的数据库服务器上工作，包括不再使用的数据库。我想删除其中一个旧数据库，它已经花费了比我预期更长的时间（超过 40 秒）然后我注意到服务器的性能受到了显着影响，甚至变得无响应。幸运的是，我最终能够终止查询并使服务器恢复稳定状态。

你能帮我理解吗：

• 性能影响的原因可能是什么？我会假设删除数据库既便宜又快速。
• 清理数据库服务器的最佳方法是什么？在删除数据库之前顺序删除每个表会更好，还是我可以做任何其他事情来安全快速地删除数据库？

谢谢！