PostgreSQL +表分区：无效的max（）和min（） [英] PostgreSQL+table partitioning: inefficient max() and min()

问题描述

我在PostgreSQL表中存储了一个巨大的分区表。每个子表在其ID上都有一个索引和一个检查约束，例如（为清楚起见，删除了不相关的细节）：

I have a huge partitioned table stored at a PostgreSQL table. Each child table has an index and a check constraint on its id, e.g. (irrelevant deatils removed for clarity):

Master table: points
    Column     |            Type             |       Modifiers        
---------------+-----------------------------+------------------------
 id            | bigint                      |
 creation_time | timestamp without time zone | 
 the_geom      | geometry                    | 


Sub-table points_01
    Column     |            Type             |        Modifiers        
---------------+-----------------------------+-------------------------
     id            | bigint                      | 
 creation_time | timestamp without time zone | 
 the_geom      | geometry                    | 

Indexes:
    "points_01_pkey" PRIMARY KEY, btree (id)
    "points_01_creation_time_idx" btree (creation_time)
    "points_01_the_geom_idx" gist (the_geom) CLUSTER
Check constraints:
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
    "id_gps_points_2010_08_22__14_47_04_check" 
               CHECK (id >= 1000000::bigint AND id <= 2000000::bigint)

现在，

SELECT max(id) FROM points_01

是即时的，但是：

SELECT max(id) FROM points

中选择max（id）

是 points_01 .. points_60 的主表，使用检查约束应该花费很少的时间，因为查询计划程序会花费一个多小时不利用检查约束。

which is a master table for points_01 .. points_60 and should take very little time using check constraints, takes more than an hour because the query planner does not utilize the check constraints.

根据PostgreSQL Wiki（此页面），这是一个已知问题，将在下一版本中解决。

According to the PostgreSQL wiki (last section of this page), this is a known issue that would be fixed in the next versions.

是否有一个很好的技巧可以使查询计划者利用 max（）和 min（）查询的检查约束和子表索引？

Is there a good hack that will make the query planner utilize the check constraints and indices of sub-tables for max() and min() queries?

谢谢

亚当

推荐答案

简短的回答：否。目前，尚无法使Postgres规划器了解某些聚合函数可以首先检查子分区的约束。对于min和max的特定情况，它相当容易证明，但对于总体而言，这是一个艰难的情况。

Short answer: No. At this point in time, there's no way to make the Postgres planner understand that some aggregate functions can check the constraints on child partitions first. Its fairly easy to prove for specific case of min and max, but for aggregates in general, its a tough case.

您始终可以将其编写为包含多个分区的UNION当它只是必须完成时...

You can always write it as a UNION of several partitions when it just has to be done...

这篇关于PostgreSQL +表分区：无效的max（）和min（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！