关于【query-performance】的问题- 第1页

gherkins

Asked: 2025-03-06 14:45:28 +0800 CST

数据库/搜索索引推荐：匹配不同类别的时间范围

5

30 万多个视频
1000 多万个标记，指向视频中的时间范围

{
  "markerCategory": "something",
  "markerDesc": "something-more-specific",
  "frameIn": 120001,
  "frameOut": 140002
},
{
  "markerCategory": "something-else",
  "markerDesc": "something-else-more-specific",
  "frameIn": 130001,
  "frameOut": 135002
}

当搜索以下内容时，任何数据库/搜索索引表现最佳的建议：

包含重叠时间范围内的事件的视频category A ，按涵盖的时间长度排序category B

视频当前从某些专有关系数据库导出并存储在 Apache SOLR 实例中以供搜索。

这些类型的查询是否有特定的名称（“反向范围查询”或类似的东西......）？
对于这些类型的查询，您有什么建议吗？哪种技术效果最好？
我在想也许是 elasticsearch？

ToC

Asked: 2024-11-20 03:53:40 +0800 CST

Sql Server：查询以解析和验证代码

6

我们有一个#ValidCode表，其中列出了有效代码，例如：“A”、“B”、“C”等。另一个名为#SourceData的表包含输入数据——它是有效和无效标记的组合（有时是重复的）。

前任：

‘A;B;C’ (有效)
‘A;A;A;A;A;B’ (有效)
'ad;df;A;B'（无效）

尝试找到一种最佳查询方法来处理这些字符串以在#SourceData中找到有效行。请参阅以下示例：

DROP TABLE IF EXISTS #ValidCode
GO
CREATE TABLE #ValidCode
(
      ID        INT             IDENTITY(1,1)
    , Code      CHAR(1)
)
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C')
GO
DROP TABLE IF EXISTS #SourceData 
GO
CREATE TABLE #SourceData 
(
      ID        INT             IDENTITY(1,1)
    , Codes     VARCHAR(500)
    , Is_Valid  BIT
    , Is_Split  BIT
)

INSERT INTO #SourceData (Codes) 
VALUES    ('A;B;C')
        , ('B;A')
        , ('B;B;B;C;C;A;A;B')
        , ('B;Z;1')
        , ('B;ss;asd')


SELECT * FROM #ValidCode
SELECT * FROM #SourceData

查询将处理#SourceData表中的数据并更新Is_Valid标志，以便它们可以在后续过程中被使用。

规则：

每个标记都必须对整个列行有效（第 1 行至第 3 行）
即使一个标记无效，整个行值也无效（第 4 行和第 5 行）

因此，这是首选的输出：

ID	代码	是否有效
1	A;B;C	1
2	B;A	1
3	B;B;B;C;C;A;A;B	1
4	B;Z;1	0
5	B;ss;asd	0

当前方法：循环遍历#SourceData中的每一行，并根据分隔符“；”将它们拆分，然后将它们与#ValidCode表进行比较。如果所有标记单独有效，则将#SourceData中的行标记为有效（Is_Valid标志）。否则标记为无效。WHILE循环方法有效，但速度很慢。

#SourceData最多可以有 300 万行。每行都有多个重复的有效值('A;A;A;A')和无效值组合('A;as;sdf;B')

有没有更好的方法？

谢谢！

Pantea

Asked: 2024-11-11 21:10:37 +0800 CST

查询删除包含 4 亿条记录的大表中 eff_date 较低的记录

9

我有一张具有以下结构的表格：

create table TEST_TAB
(
  activity_type CHAR(1),
  tracking_code NUMBER,
  eff_date      DATE
)

该表的示例数据：

insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));

这只是示例数据，原始表中的实际数据量接近 4 亿条记录。我需要做的是，对于每组activity_type, tracking_code，我需要保留具有最高“eff_date”的记录并删除其余记录。因此，对于，activity_type=A and tracking_code = 1我需要保留具有的记录eff_date = 1/2/2024并删除另一个记录。我现在有以下查询：

delete from test_tab
 where rowid in (select rid
                   from (select rowid as rid,
                                row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
                           from test_tab
                           )
                  where row_num > 1
                  )

但是这似乎很慢。您能提出更好的解决方案吗？原始表按 eff_date 进行分区，并在其余两列上建立索引。另一点是，单个组中每条记录的 eff_date 之间可能相差一年以上。

提前致谢

Sicilian-Najdorf

Asked: 2024-10-09 17:09:35 +0800 CST

自动更新统计 - 错误的查询计划

7

我有一个查询，涉及 14 亿行的表。

此查询一切运行正常，运行时间不到一秒。

然后大约每 5 天一次，自动更新统计数据开始启动，查询开始运行近 60 秒 - 它采用不同的计划。

我检查了一下，当自动更新统计数据开始时，采样率低于表格的 1％。

现在，我的问题是 - 自动更新统计数据是否会导致优化程序选择比以前更糟糕的计划？

我可以看到，糟糕计划的估计与好计划的估计有很大不同。

好计划：

坏计划：

对于糟糕的计划来说，估计显然是错误的，但查询返回的行数是相同的。

此时，我想确认自动统计更新是否可以做到这一点？

我现在尝试使用 10% 的样本进行统计数据更新，然后观察会发生什么。

更新： 我已经完成了 10% 采样率的统计更新，查询再次运行良好。

已将此工作定期进行。

Stark

Asked: 2024-10-08 00:20:06 +0800 CST

Azure SQL：hierarchyid 性能问题

11

（抱歉文章太长——这是 SE 上的第一篇帖子）

我已经在各种应用程序中使用 HierarchyID 类型大约 10 年了，总体上我对它的性能很满意。我偶尔会遇到有关 HierarchyID 性能不佳的报告，并且总是认为这是由于索引配置不正确或可以优化的查询造成的。但现在我自己也遇到了这个问题。

Azure SQL，数据库兼容级别设置为 160（SQL Server 2022）。

我有下表：

create table [Dim]
(
    [Key] [int] not null,
    [Path] [hierarchyid] null,
    [Name] [nvarchar](256) null,
    [ParentKey] [int] null,
    [SortOrder] [int] null,
    [IsLeaf] [bit] null,
    constraint PK_Dim primary key clustered ([Key]),
    index IX_Dim_Name unique ([Name]) include ([Path]),
    index IX_Dim_ParentKey unique ([ParentKey], [SortOrder]),
    index IX_Dim_Path ([Path]),
    index IX_Dim_Leaf ([IsLeaf], [Path])
)

该表旨在模拟父子层次结构。Path 字段基于父 Path 和当前成员的 SortOrder 反映层次结构。测试数据集中有 10,010 条记录，最大层次结构深度为 8。我们运行以下查询：

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].IsDescendantOf(r.[Path]) = 1
        and d.[IsLeaf] = 1);

该查询执行耗时 14 秒，返回 3,053 行。子查询返回 1,298 行。以下是执行计划：和链接：https ://www.brentozar.com/pastetheplan/?id=SydK3qIk1l

它看上去和我预期的差不多，除了 IX_Dim_Leaf 上的索引扫描，它读取了 6,861,228 行。

现在，如果向表中添加一个附加列，该列包含路径字段的字符串表示形式以及新字段上相应的两个索引：

alter table [Dim] add [PathStr] varchar(256);
go
update [Dim] set [PathStr] = [Path].ToString();
create index [IX_Dim_PathStr] on [Dim] ([PathStr]);
create index [IX_Dim_LeafStr] on [Dim] ([IsLeaf], [PathStr]);
go

然后重写查询以使用 PathStr 而不是 Path：

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[PathStr]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[PathStr]
    from [Dim] d
    inner join [Dim] p on (d.[PathStr] like p.[PathStr] + '%')
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[PathStr] like r.[PathStr] + '%'
    and d.[IsLeaf] = 1);

新查询执行时间为 0.064 秒。执行计划如下：链接：https://www.brentozar.com/pastetheplan/? id=Sy5oT5Uyye

由于新字段未包含在 IX_Dim_Name 索引中并且必须添加“％”字符串，因此它实际上比第一个查询计划更复杂，但最大的区别在于外部索引扫描，其中只读取了 3053 行而不是 6.8 百万行。

对我来说，字符串字段的性能优于理论上针对此类分层查询进行了优化的 HierarchyID 字段没有任何意义。是我做错了什么，还是 SQL Server 根本无法处理子查询中的 HierarchyID，我们应该坚持使用字符串字段？

注意：将子查询的结果存储在表 var 中，然后将表 var 与 Dim 表连接起来，在使用 Hierarchyid 时实际上性能会更好一些，但不幸的是这不是一个选项。

编辑：按照 Charlieface 的以下建议，我也尝试了这个查询：

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].GetAncestor(1) = r.[Path]
        and d.[IsLeaf] = 1);

执行时间为 38 毫秒（计划执行）。看来只有 IsDescendantOf() 有问题。

Enzo Damato

Asked: 2024-09-10 07:46:40 +0800 CST

在“select * where col=x”中，其中 x 是不可排序的、非索引的列，数据库是否会比平面文件具有任何性能提升？

5

如果我正在查询数据库以选择具有VARCHAR()与查询参数相同的字段的所有记录。对平面文件“数据库”进行逐行线性搜索是否有速度优势？如果有，这种加速是如何实现的？

Elikill58

Asked: 2024-08-28 20:08:56 +0800 CST

如何优化使用不同静态字符串的多个 WHEN？

5

我有一张这样的桌子：

ID	笔记	客户
1	有多行的长注释客户：姓名	无效的
2	附注：有多行客户：姓名	无效的
3	第三条注释有多行客户：姓名行	无效的
4	线路：客户：姓名线	无效的
5	B 行 B : 行客户 : 姓名行其他	无效的

我想从“注释”列中提取客户信息，然后仅使用一个 SQL 查询将其放在“客户”列中。

我给出了一些代表我实际拥有的数千行代码的示例。我首先进行了以下查询：

SELECT
    CASE
        WHEN posclient != 0 THEN SUBSTRING(
            Note,
            posclient + LENGTH('Client: '),
            LOCATE('\n', Note, posclient + LENGTH('Client: ')) - (posclient + LENGTH('Client: '))
        )
        WHEN poscustomer != 0 THEN SUBSTRING(
            Note,
            poscustomer + LENGTH('Customer : '),
            LOCATE('\n', Note, poscustomer + LENGTH('Customer : ')) - (poscustomer + LENGTH('Customer : '))
        )
        WHEN poscontact != 0 THEN SUBSTRING(
            Note,
            poscontact + LENGTH('Contact : '),
            LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
        )
        ELSE ''
    END AS client,
    Id
FROM (SELECT Id, Note,
        LOCATE('Customer : ', Note) as poscustomer,
        LOCATE('Client: ', Note) as posclient,
        LOCATE('Contact : ', Note) as poscontact
    FROM myTable) x 
WHERE (poscustomer != 0 OR posclient != 0 OR poscontact != 0)

由于存在重复的代码（WHEN部分），如果我有另一种添加方法，则很难阅读并且不太好。

我有：至少有 6 种编写客户端的方法。我想优化这部分：

WHEN poscontact != 0 THEN SUBSTRING(
   Note,
   poscontact + LENGTH('Contact : '),
   LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
)

是“通用的”，但我不知道怎么做，因为我不懂编程语言。在 php 中，这很容易，但我不能使用编程语言，我只能使用一个查询。

有一件事非常有用：每行只有一种方法可以编写客户端。它始终只包含一个。

我该怎么办？

Parker

Asked: 2024-08-08 21:50:13 +0800 CST

在 PostgreSQL 中对单个索引文本列进行计数时出现这种间歇性慢查询的原因是什么？

5

我有一张包含 2,395,015 行的表，其中一TEXT列有三个值之一，并且从不为NULL。在计算值与大多数 (>99%) 行匹配的行数时，我遇到了间歇性查询性能问题。我想修复这个性能问题。这些查询必须返回精确计数，因此我不能使用近似计数。

corpus=# \d metadata
                             Table "public.metadata"
    Column     |            Type             | Collation | Nullable |    Default
---------------+-----------------------------+-----------+----------+----------------
 id            | text                        |           | not null |
 priority      | integer                     |           | not null | 10
 media_type    | text                        |           | not null |
 modified      | timestamp without time zone |           | not null | now()
 processed     | timestamp without time zone |           |          |
 status        | text                        |           | not null | 'QUEUED'::text
 note          | text                        |           |          |
 content       | text                        |           |          |
 resolved      | text                        |           |          |
 response_time | integer                     |           |          |
 luid          | integer                     |           | not null |
 jamo_date     | timestamp without time zone |           |          |
 audit_path    | text                        |           |          |
Indexes:
    "metadata_pkey" PRIMARY KEY, btree (id)
    "metadata_id_idx" btree (id)
    "metadata_luid_idx" btree (luid)
    "metadata_modified_idx" btree (modified DESC)
    "metadata_processed_idx" btree (processed DESC)
    "metadata_status_idx" btree (status)
Check constraints:
    "media_type_ck" CHECK (media_type = ANY (ARRAY['text/json'::text, 'text/yaml'::text]))
    "status_ck" CHECK (status = ANY (ARRAY['QUEUED'::text, 'PROCESSED'::text, 'ERROR'::text]))
Foreign-key constraints:
    "metadata_luid_fkey" FOREIGN KEY (luid) REFERENCES concept(luid) ON DELETE CASCADE

corpus=#

QUEUED我有一些简单的查询，用于计算与三个状态代码（、PROCESSED、）之一匹配的行数ERROR。匹配的行数为 0 行QUEUED，匹配的行数为 9,794 行ERROR，匹配的行数为 2,385,221 行PROCESSED。当我针对每个状态代码运行相同的查询时，通常会立即得到一组结果：

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='QUEUED';
                                                                          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1947.17..1947.18 rows=1 width=8) (actual time=2.935..2.936 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..1915.97 rows=12480 width=0) (actual time=2.932..2.933 rows=0 loops=1)
         Output: status
         Index Cond: (metadata.status = 'QUEUED'::text)
         Heap Fetches: 0
 Planning Time: 0.734 ms
 Execution Time: 2.988 ms
(8 rows)

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='ERROR';
                                                                             QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1184.19..1184.20 rows=1 width=8) (actual time=1484.763..1484.764 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..1165.26 rows=7569 width=0) (actual time=4.235..1484.029 rows=9794 loops=1)
         Output: status
         Index Cond: (metadata.status = 'ERROR'::text)
         Heap Fetches: 9584
 Planning Time: 0.072 ms
 Execution Time: 1484.786 ms
(8 rows)

corpus=#

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='PROCESSED';
                                                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=261398.83..261398.84 rows=1 width=8) (actual time=741.319..749.026 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=261398.62..261398.83 rows=2 width=8) (actual time=741.309..749.020 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=260398.62..260398.63 rows=1 width=8) (actual time=735.099..735.100 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=730.871..730.872 rows=1 loops=1
               Worker 1: actual time=733.435..733.436 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..257903.37 rows=998100 width=0) (actual time=0.065..700.529 rows=795074 loops=3)
                     Output: status
                     Index Cond: (metadata.status = 'PROCESSED'::text)
                     Heap Fetches: 747048
                     Worker 0: actual time=0.060..702.980 rows=670975 loops=1
                     Worker 1: actual time=0.076..686.946 rows=1010099 loops=1
 Planning Time: 0.085 ms
 Execution Time: 749.068 ms
(18 rows)

corpus=#

但有时，计算PROCESSED行数会花费过多的时间（有时需要几分钟）：

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='PROCESSED';
                                                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=261398.83..261398.84 rows=1 width=8) (actual time=30019.273..30019.336 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=261398.62..261398.83 rows=2 width=8) (actual time=30019.261..30019.326 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=260398.62..260398.63 rows=1 width=8) (actual time=29967.734..29967.735 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=29939.915..29939.916 rows=1 loops=1
               Worker 1: actual time=29944.395..29944.395 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..257903.37 rows=998100 width=0) (actual time=75.385..29931.795 rows=795074 loops=3)
                     Output: status
                     Index Cond: (metadata.status = 'PROCESSED'::text)
                     Heap Fetches: 747151
                     Worker 0: actual time=128.857..29899.156 rows=916461 loops=1
                     Worker 1: actual time=28.609..29905.708 rows=854439 loops=1
 Planning Time: 421.203 ms
 Execution Time: 30019.440 ms
(18 rows)

corpus=#

虽然上述查询运行缓慢，但我能够针对其他两个代码中的任一个查询同一张表，并且这些查询在 1 秒内返回。我查找了表锁（没有）。即使没有其他查询或表插入正在运行，也会发生这种情况。

这些间歇性慢速查询的可能原因有哪些？
我可以尝试哪些额外的调试来获取有关这些慢速查询的更多信息？
有没有相关的服务器设置？
是否有更有效的方法来索引/编码这些列（例如，我应该使用CHAR(1)），甚至是SMALLINT？如果是这样，应该为该列使用什么索引？

如果我使用CHAR(1)，以下约束之间是否有区别：

ALTER TABLE jgi_metadata ADD CONSTRAINT status_code_ck CHECK (status_code = ANY (ARRAY['Q'::char(1), 'P'::char(1), 'E'::char(1)]));
ALTER TABLE jgi_metadata ADD CONSTRAINT status_code_ck CHECK (status_code IN ('Q', 'P', 'E'));
是否可以对该列使用部分索引，即使它从来没有被使用过NULL？
我是否应该将其PROCESSED拆分为布尔列，然后status仅将该列用于其他代码并使用部分索引使其可空？

这是在 Linux 上运行的具有默认设置的 PostgreSQL 11。

我还尝试过其他方法：

将 work_mem 增加到 100MB（通过postgresql.conf）。性能没有变化。
我尝试在状态列上创建部分索引。

更新：我发现这个性能问题与状态列无关，而是与表本身的大小有关，如以下 2 分钟查询所示：

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata;
                                                                                            QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=196398.52..196398.53 rows=1 width=8) (actual time=118527.897..118554.762 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=196398.30..196398.51 rows=2 width=8) (actual time=118522.165..118554.756 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=195398.30..195398.31 rows=1 width=8) (actual time=118491.043..118491.044 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=118475.143..118475.144 rows=1 loops=1
               Worker 1: actual time=118476.110..118476.111 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..192876.13rows=1008870 width=0) (actual time=71.797..118449.265 rows=809820 loops=3)
                     Output: status
                     Heap Fetches: 552630
                     Worker 0: actual time=75.877..118434.476 rows=761049 loops=1
                     Worker 1: actual time=104.872..118436.647 rows=745770 loops=1
 Planning Time: 592.040 ms
 Execution Time: 118554.839 ms
(17 rows)

corpus=#

这似乎与现在的其他问题非常相似，所以我正在尝试从这个答案中采取缓解策略：

VACUUM ANALYZE metadata;第一次COUNT(*)计数耗时 5 秒，后续计数耗时 190 毫秒。

其他想法：

如果将状态列拆分成其自己的表，并在metadata表中设置外键，这会有帮助吗？

注意：我越来越相信这个问题与这里的其他几个问题重复：

这个答案可能是该问题的最佳解决方案：

https://stackoverflow.com/a/7945274/2074605

根据要求，这里是带有缓冲区的查询计划分析：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT COUNT(*) FROM metadata;

                                                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=80771.95..80771.96 rows=1 width=8) (actual time=26711.481..26716.494 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=293915 read=19595 dirtied=282 written=12
   ->  Gather  (cost=80771.73..80771.94 rows=2 width=8) (actual time=26711.203..26716.488 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=293915 read=19595 dirtied=282 written=12
         ->  Partial Aggregate  (cost=79771.73..79771.74 rows=1 width=8) (actual time=26565.622..26565.623 rows=1 loops=3)
               Output: PARTIAL count(*)
               Buffers: shared hit=293915 read=19595 dirtied=282 written=12
               Worker 0: actual time=26530.890..26530.891 rows=1 loops=1
                 Buffers: shared hit=105264 read=6760 dirtied=145 written=5
               Worker 1: actual time=26530.942..26530.942 rows=1 loops=1
                 Buffers: shared hit=84675 read=7529 dirtied=46 written=2
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..77241.05 rows=1012275 width=0) (actual time=42.254..26529.232 rows=809820 loops=3)
                     Output: status
                     Heap Fetches: 17185
                     Buffers: shared hit=293915 read=19595 dirtied=282 written=12
                     Worker 0: actual time=59.291..26494.376 rows=815113 loops=1
                       Buffers: shared hit=105264 read=6760 dirtied=145 written=5
                     Worker 1: actual time=31.165..26484.729 rows=1036972 loops=1
                       Buffers: shared hit=84675 read=7529 dirtied=46 written=2
 Planning Time: 98.400 ms
 Execution Time: 26716.529 ms
(25 rows)

Doe Jowns

Asked: 2024-06-15 19:21:42 +0800 CST

PostgreSQL 中 CLUSTER 的性能不稳定

5

我使用的是 PostgreSQL 12，并且有分区表，我想将它们聚类。每个分区的大小大致相同（以 GB 为单位）。但是，性能可能会有很大差异，每个分区的聚类时间从 5 分钟到 1000 分钟不等。

我正在并行对多个分区进行集群，并且我的数据库当前没有收到除 CLUSTER 请求之外的任何请求。

我不完全了解 CLUSTER 过程的来龙去脉。如何解释聚类时间如此大的变化？我可以做些什么来提高性能？

编辑：更准确地说，我有一个 34 GB 的分区，在 13 分钟内完成聚类，还有一个 56 GB 的分区，在 1288 分钟内（几乎一天）完成聚类。这些大小值是聚类前的大小。我正在重新计算大小。

用于检索大小的查询：

SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_schema || '."' || table_name || '"')) AS size
FROM information_schema.tables
WHERE table_schema = 'partitionschema' and table_name like 'mytable_%' order by table_name;

不过，在聚类之前我没有执行 ANALYZE 或 VACUUM。

我有 900MB/s 的磁盘 I/O 和 128GB 的 RAM（尽管根据我的 datadog 仪表板，似乎只使用了 40 左右）。我的处理器似乎也不是一个限制。

我有 300 个分区需要集群，在流程的每个步骤中，我都尝试并行集群 8 个分区。每个分区都在 ~50GB 中

其他一些信息：

维护工作内存：4GB
工作模因：64MB
共享缓冲区：32GB
最大wal大小：4GB

user570286

Asked: 2024-05-08 05:07:23 +0800 CST

如何找到最上面的行并仅通过一次扫描来计算行数？

7

假设我有一个如下所示的架构：

-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);

-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
  SELECT i, c1, c2 FROM t1 WHERE c1 % 2 == 0;
-- The real WHERE clause is slightly more complex.

假设该表t1包含数百万行：

INSERT INTO t1(i, c1, c2)
  SELECT value, random(), random() FROM generate_series(1, 5000000);

t1假设我想获取具有最高偶数的行的索引以及具有偶数的c1行的计数：c1c2

SELECT
  (SELECT i FROM t1_filtered ORDER BY c1 DESC LIMIT 1),
  (SELECT count(*) FROM t1_filtered WHERE c2 % 2 == 0);

真正的ORDER BY子句要复杂得多，但这足以说明我的问题。

在我看来，这应该只需要一次扫描就可以实现t1，但是EXPLAIN QUERY PLAN说这个查询扫描了t1两次：

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCALAR SUBQUERY 2
   `--SCAN t1

如果我加入两个子查询而不是将它们写为结果列，那么查询计划会有所不同，但仍然有两次扫描t1：

QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
|--MATERIALIZE (subquery-2)
|  `--SCAN t1
|--SCAN (subquery-1)
`--SCAN (subquery-2)

无论如何，我希望这个查询是这样的伪代码：

var top_row = {i: NULL, c1: 0};
var count = 0;
for each {i, c1, c2} in t1:
  if c1 % 2 == 0:
    if c1 > top_row.c1:
      top_row = {i, c1};
    if c2 % 2 == 0:
      count = count + 1;
return {top_row.i, count};

我怎样才能让查询规划器知道这只需要一次扫描？

更新，2024-05-09：我尝试了Charlieface 提出的查询。根据EXPLAIN QUERY PLAN，它确实使查询规划器仅使用一次扫描t1......

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  |--SCAN t1
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN t

...但它的运行速度明显比我的原始查询慢：使用测试SQLite REPLSELECT (...), (...)中的两个查询，我发现（对于包含 500 万行随机数据的示例表）我的原始查询的平均运行时间为 2.22 秒样本标准差为 0.03 秒，该查询的平均值为 5.57 秒。开发人员。0.23秒。.timer ont1

我的直觉直觉并没有向我建议，但研究查询计划的“USE TEMP B-TREE FOR ORDER BY”确实建议我在t1(c1). 这确实加快了我原来的查询速度，使得 st 平均需要 1.09 秒。开发人员。0.02秒。然而，令我惊讶的是，索引显然使 Charlieface 的查询花费了更长的时间，甚至可能使其无法终止——我在等待 108 秒后中断了它，然后在重试 32 秒后中断了它，然后我没有'不要再试一次。

Charlieface 的查询确实通过将扫描次数减少t1到 1 来回答了我的问题，但相对于我原来的查询，它的实际性能较差，使我不愿意接受它作为答案。我希望这不会“移动球门柱”太多。我确实将其标记为query-performance，因此性能从一开始就是我问题的一部分。

更新，2024-05-09 #2：使用CREATE INDEX t1_c1 ON t1(c1)，我的原始查询的查询计划变为

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX t1_c1
`--SCALAR SUBQUERY 2
   `--SCAN t1

Charlieface 查询的查询计划变为

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX t1_c1
|  `--SCAN (subquery-4)
`--SCAN t

根据Charlieface 的评论CREATE INDEX index_per_comment660077_339327 ON t1 (c1 DESC) WHERE (c1 % 2 = 0)（SQLite 不支持），查询计划分别变为：INCLUDE

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX index_per_comment660077_339327
`--SCALAR SUBQUERY 2
   `--SCAN t1 USING INDEX index_per_comment660077_339327

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX index_per_comment660077_339327
|  `--SCAN (subquery-4)
`--SCAN t

数据库/搜索索引推荐：匹配不同类别的时间范围

Sql Server：查询以解析和验证代码

查询删除包含 4 亿条记录的大表中 eff_date 较低的记录

自动更新统计 - 错误的查询计划

Azure SQL：hierarchyid 性能问题

在“select * where col=x”中，其中 x 是不可排序的、非索引的列，数据库是否会比平面文件具有任何性能提升？

如何优化使用不同静态字符串的多个 WHEN？

在 PostgreSQL 中对单个索引文本列进行计数时出现这种间歇性慢查询的原因是什么？

PostgreSQL 中 CLUSTER 的性能不稳定

如何找到最上面的行并仅通过一次扫描来计算行数？

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

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

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

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

列出指定表的所有列

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

你如何mysqldump特定的表？

使用 psql 列出数据库权限

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

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

问题[query-performance](dba)