AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题

问题[postgresql-performance](dba)

Martin Hope
Bart Jonk
Asked: 2023-05-11 21:28:37 +0800 CST

postgresql Large Join 不使用索引

  • 6

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

在这里查询和计划: 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");

但未使用索引(请参阅查询计划)。

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

postgresql-performance
  • 1 个回答
  • 43 Views
Martin Hope
KonAggelos
Asked: 2023-05-02 20:27:05 +0800 CST

问题:简单的查询需要很长时间才能完成(超过 7 分钟)

  • 3


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。

postgresql-performance
  • 1 个回答
  • 43 Views
Martin Hope
Macchiatow
Asked: 2023-04-10 23:41:39 +0800 CST

Postgres 中的多键索引

  • 5

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

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

简单的查询可能看起来像

SELECT * 
from table 
WHERE email = 'some@email.com'

或者

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

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

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

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

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

postgresql-performance
  • 1 个回答
  • 19 Views
Martin Hope
TheProgrammer
Asked: 2022-10-12 18:53:55 +0800 CST

在单个表上运行数千个更新查询的最佳方法

  • 0

我有一个脚本,它运行大约 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
.......
postgresql postgresql-performance
  • 2 个回答
  • 55 Views
Martin Hope
Sheikh Wasiu Al Hasib
Asked: 2022-09-26 21:30:48 +0800 CST

PostgreSQL 数据库频繁运行 AUTO VACUUM 有影响吗?

  • 0

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

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

postgresql postgresql-performance
  • 1 个回答
  • 46 Views
Martin Hope
Gismat Husein
Asked: 2022-09-24 05:12:30 +0800 CST

如何优化向 PostgreSQL 插入 10+ 百万数据

  • 0

我有一张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)

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

postgresql postgresql-performance
  • 1 个回答
  • 67 Views
Martin Hope
GabrielNexT
Asked: 2022-09-21 11:36:35 +0800 CST

如何避免左连接中的条件并优化查询?

  • 1

我在工作中遇到了一点问题,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 行
postgresql postgresql-performance
  • 2 个回答
  • 71 Views
Martin Hope
Gismat Husein
Asked: 2022-09-21 03:50:15 +0800 CST

如何优化 PostgreSQL 中十亿行的 Where 子句?

  • 1

所以这是我第一次接触大数据。我有一张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)
postgresql postgresql-performance
  • 2 个回答
  • 350 Views
Martin Hope
Mahmoud
Asked: 2022-09-19 03:25:12 +0800 CST

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

  • -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
  • 2 个回答
  • 65 Views
Martin Hope
MrKnista
Asked: 2022-09-17 02:00:46 +0800 CST

Postgres:删除数据库会影响性能

  • 0

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

你能帮我理解吗:

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

谢谢!

postgresql postgresql-performance
  • 2 个回答
  • 41 Views

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

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

    • 12 个回答
  • Marko Smith

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

    • 3 个回答
  • Marko Smith

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

    • 3 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

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

    • 4 个回答
  • Marko Smith

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

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve