PostgreSQL 11在分区表上进行并行顺序扫描，而索引应该已经足够。

Question

问题是我在一个非常简单的查询中，针对一个非常琐碎的设置，总是得到序列扫描。我做错了什么吗？

• Windows Server 2016 上的Postgres 11
• 所做的配置更改：constraint_exclusion = partition
• 一个单表分成了200个子表，每个分区有数百万条记录。
• 针对涉及字段的索引（假定一个也被分区）

这是创建语句：

CREATE TABLE A (
    K int NOT NULL,
    X bigint NOT NULL,
    Date timestamp NOT NULL,
    fy smallint NOT NULL,
    fz decimal(18, 8) NOT NULL,
    fw decimal(18, 8) NOT NULL,
    fv decimal(18, 8) NULL,
    PRIMARY KEY (K, X)
) PARTITION BY LIST (K);

CREATE TABLE A_1 PARTITION OF A FOR VALUES IN (1);
CREATE TABLE A_2 PARTITION OF A FOR VALUES IN (2);
...
CREATE TABLE A_200 PARTITION OF A FOR VALUES IN (200);
CREATE TABLE A_Default PARTITION OF A DEFAULT;

CREATE INDEX IX_A_Date ON A (Date);

需要翻译的内容：

涉及的查询：

SELECT K, MIN(Date), MAX(Date)
FROM A 
GROUP BY K

这总是会产生一个序列扫描，需要几分钟的时间，尽管很明显根本不需要表数据，因为日期字段已被索引，并且我只是在请求其B树的第一个和最后一个叶子节点。

最初索引是在 (K, Date) 上，它迅速向我呈现了Postgres不会在任何查询中使用我认为会使用的索引。在 (Date) 上的索引为其他查询提供了解决方案，似乎Postgres声称自动对索引进行分区。然而，这个特定的简单查询总是进行顺序扫描。

欢迎任何想法！

更新

查询计划 (分析、缓冲区) 如下：

Finalize GroupAggregate  (cost=4058360.99..4058412.66 rows=200 width=20) (actual time=148448.183..148448.189 rows=5 loops=1)
  Group Key: a_16.k
  Buffers: shared hit=5970 read=548034 dirtied=4851 written=1446
  ->  Gather Merge  (cost=4058360.99..4058407.66 rows=400 width=20) (actual time=148448.166..148463.953 rows=8 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    Buffers: shared hit=5998 read=1919356 dirtied=4865 written=1454
    ->  Sort  (cost=4057360.97..4057361.47 rows=200 width=20) (actual time=148302.271..148302.285 rows=3 loops=3)
        Sort Key: a_16.k
        Sort Method: quicksort  Memory: 25kB
        Worker 0:  Sort Method: quicksort  Memory: 25kB
        Worker 1:  Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=5998 read=1919356 dirtied=4865 written=1454
        ->  Partial HashAggregate  (cost=4057351.32..4057353.32 rows=200 width=20) (actual time=148302.199..148302.203 rows=3 loops=3)
            Group Key: a_16.k
            Buffers: shared hit=5984 read=1919356 dirtied=4865 written=1454
            ->  Parallel Append  (cost=0.00..3347409.96 rows=94658849 width=12) (actual time=1.678..116664.051 rows=75662243 loops=3)
                Buffers: shared hit=5984 read=1919356 dirtied=4865 written=1454
                ->  Parallel Seq Scan on a_16  (cost=0.00..1302601.32 rows=42870432 width=12) (actual time=0.320..41625.766 rows=34283419 loops=3)
                    Buffers: shared hit=14 read=873883 dirtied=14 written=8
                ->  Parallel Seq Scan on a_19  (cost=0.00..794121.94 rows=26070794 width=12) (actual time=0.603..54017.937 rows=31276617 loops=2)
                    Buffers: shared read=533414
                ->  Parallel Seq Scan on a_20  (cost=0.00..447025.50 rows=14900850 width=12) (actual time=0.347..52866.404 rows=35762000 loops=1)
                    Buffers: shared hit=5964 read=292053 dirtied=4850 written=1446
                ->  Parallel Seq Scan on a_18  (cost=0.00..198330.23 rows=6450422 width=12) (actual time=4.504..27197.706 rows=15481014 loops=1)
                    Buffers: shared read=133826
                ->  Parallel Seq Scan on a_17  (cost=0.00..129272.31 rows=4308631 width=12) (actual time=3.014..18423.307 rows=10340224 loops=1)
                    Buffers: shared hit=6 read=86180 dirtied=1
                ...
                ->  Parallel Seq Scan on a_197  (cost=0.00..14.18 rows=418 width=12) (actual time=0.000..0.000 rows=0 loops=1)
                ->  Parallel Seq Scan on a_198  (cost=0.00..14.18 rows=418 width=12) (actual time=0.001..0.002 rows=0 loops=1)
                ->  Parallel Seq Scan on a_199  (cost=0.00..14.18 rows=418 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                ->  Parallel Seq Scan on a_default  (cost=0.00..14.18 rows=418 width=12) (actual time=0.001..0.002 rows=0 loops=1)
Planning Time: 16.893 ms
Execution Time: 148466.519 ms

更新2 仅为避免未来出现“你应该在（K，日期）上建索引”的评论：

如果两个索引都存在，则查询计划完全相同，分析数字相同，甚至缓冲区命中/读取几乎相同。

- Vitaly

---

您的查询请求从所有分区中获取所有行，因此索引很可能没有帮助。此外，您的索引仅包含“date”列，而不包含“K”列，因此Postgres需要使用随机I/O查找每个“date”值的“K”值，这很可能比顺序扫描慢。您可以尝试在“k，date”上建立索引。 “random_page_cost”的值是多少？如果您确定随机I/O会更快，则降低该值可能会说服规划器优先选择索引扫描。 - user330315

回到 (K, Date) 的索引是我尝试的第一件事，但没有起到任何作用。 - Vitaly

我做错了什么？你使用的是Windows吗？你将日期用作标识符（用于时间戳）？ - wildplasser

X（bigint）用于标识符，我使用日期作为日期，因为我需要在此处有一个日期。而Windows...毕竟它是否相关？ - Vitaly

时序确实看起来很慢。从共享内存中读取1500万行需要27秒并不正确。但是从磁盘中读取也似乎相当缓慢：292053个块或2GB需要52秒 - 可能是由于Windows的NTFS不是最快的文件系统导致的。IO性能慢的原因之一可能是病毒扫描程序。但我不知道什么会使从缓存访问块变得如此缓慢。该服务器有多少个CPU？也许您可以通过增加“max_parallel_workers_per_gather”和“max_parallel_workers”来稍微缓解问题。 - user330315

我的关注点并不在系统的整体性能上，而是关于Postgres选择执行查询的查询计划。 - Vitaly

Answer 1

将聚合下推到并行计划中可以通过将enable_partitionwise_aggregate设置为on来启用。

这可能会加快查询的速度，因为PostgreSQL不必在并行工作进程之间传递太多数据。

但是看起来PostgreSQL并不聪明，不能弄清楚它可以使用索引加速每个分区的min和max，尽管它足够聪明以处理非分区表。

没有什么好的方法可以解决这个问题，你可以查询每个分区：

SELECT k, min(min_date), max(max_date)
FROM (
   SELECT 1 AS k, MIN(date) AS min_date, MAX(date) AS max_date FROM a_1
   UNION ALL
   SELECT 2, MIN(date), MAX(date) FROM a_2
   UNION ALL
   ...
   SELECT 200, MIN(date), MAX(date) FROM a_200
   UNION ALL
   SELECT k, MIN(date), MAX(date) FROM a_default
) AS all_a
GROUP BY k;

呃，这里显然还有改进的空间。

我深入研究了代码，并在src/backend/optimizer/plan/planagg.c中找到了原因：

/*
 * preprocess_minmax_aggregates - preprocess MIN/MAX aggregates
 *
 * Check to see whether the query contains MIN/MAX aggregate functions that
 * might be optimizable via indexscans.  If it does, and all the aggregates
 * are potentially optimizable, then create a MinMaxAggPath and add it to
 * the (UPPERREL_GROUP_AGG, NULL) upperrel.
[...]
 */
void
preprocess_minmax_aggregates(PlannerInfo *root, List *tlist)
{
[...]                                                                                
    /*
     * Reject unoptimizable cases.
     *
     * We don't handle GROUP BY or windowing, because our current
     * implementations of grouping require looking at all the rows anyway, and
     * so there's not much point in optimizing MIN/MAX.
     */
    if (parse->groupClause || list_length(parse->groupingSets) > 1 ||
        parse->hasWindowFuncs)
        return;

基本上，当PostgreSQL看到GROUP BY子句时会放弃执行。

- Laurenz Albe

---

"呃！这里显然有改进的空间。话题发起者可以使用PostgreSQL支持的动态SQL生成内部SQL。" - Raymond Nijland

我现在注意到你正在混合使用非聚合列和聚合列，这是SQL标准不允许的，因此很可能PostgreSQL会在你的查询中出现错误。 - Raymond Nijland

谢谢，那是一处遗漏。已修复。 - Laurenz Albe

没问题，我应该在外部SQL语句中说，在内部SQL语句是正确的，因为那里的列是常量，所以允许在那里使用。 - Raymond Nijland

谢谢！这解释得很清楚，我在使用Postgre时会记住这个细微差别。 - Vitaly