PostgreSQL使用连接表进行分区 – 在查询计划中不使用分区约束

发布时间：2020-12-13 15:57:42 所属栏目：百科来源：网络整理

导读：我在PostgreSQL 9.2中有一个大表,我将其分区为 described in the manual.嗯……差不多！我的真实分区键不在分区表本身中,而是在连接表中,如下所示(简化)： -- millions to tens of millions of rowsCREATE TABLE data( slice_id integer NOT NULL,point_id i

我在PostgreSQL 9.2中有一个大表,我将其分区为 described in the manual.嗯……差不多！我的真实分区键不在分区表本身中,而是在连接表中,如下所示(简化)：

-- millions to tens of millions of rows
CREATE TABLE data
(
  slice_id integer NOT NULL,point_id integer NOT NULL,-- ... data columns ...,CONSTRAINT pk_data PRIMARY KEY (slice_id,point_id),CONSTRAINT fk_data_slice FOREIGN KEY (slice_id) REFERENCES slice (id)
  CONSTRAINT fk_data_point FOREIGN KEY (point_id) REFERENCES point (id)
)

-- hundreds to thousands of rows
CREATE TABLE slice
(
  id serial NOT NULL,partition_date timestamp without time zone NOT NULL,other_date timestamp without time zone NOT NULL,int_key integer NOT NULL
  CONSTRAINT pk_slice PRIMARY KEY (id)
)

-- about 40,000 rows
CREATE TABLE point
(
  -- ... similar to "slice" ...
)

要分区的表(数据)包含每个点和切片组合的行,每个组合都有一个复合键.我想只在其中一个键列partition_date上进行分区,它是slice的一部分.当然,我的子表上的检查约束不能直接包含它,所以我包含与该partition_date对应的所有slice.id值的范围,如下所示：

ALTER TABLE data_part_123 ADD CONSTRAINT ck_data_part_123
    CHECK (slice_id >= 1234 AND slice_id <= 1278);

这一切都适用于插入数据.但是,查询不使用上面的CHECK约束.例如.

SELECT *
FROM data d
JOIN slice s ON d.slice_id = s.id
WHERE s.partition_date = '2013-07-23'

我可以在查询计划中看到,它仍会扫描所有子表.我试过用几种方法重写查询,包括CTE和子选择,但这没有帮助.

有什么方法可以让规划师“理解”我的分区方案吗？我真的不想在数据表中复制数百万次分区密钥.

查询计划如下所示：

Aggregate  (cost=539243.88..539243.89 rows=1 width=0)
  ->  Hash Join  (cost=8.88..510714.02 rows=11411945 width=0)
        Hash Cond: (d.slice_id = s.id)
        ->  Append  (cost=0.00..322667.41 rows=19711542 width=4)
              ->  Seq Scan on data d  (cost=0.00..0.00 rows=1 width=4)
              ->  Seq Scan on data_part_123 d  (cost=0.00..135860.10 rows=8299610 width=4)
              ->  Seq Scan on data_part_456 d  (cost=0.00..186807.31 rows=11411931 width=4)
        ->  Hash  (cost=7.09..7.09 rows=143 width=4)
              ->  Seq Scan on slice s  (cost=0.00..7.09 rows=143 width=4)
                    Filter: (partition_date = '2013-07-23 00:00:00'::timestamp without time zone)

解决方法

实现它的唯一方法是使查询动态化：

create function select_from_data (p_date date)
returns setof data as $function$

declare
    min_slice_id integer,max_slice_id integer;

begin
    select min(slice_id),max(slice_id)
    into min_slice_id,max_slice_id
    from slice
    where partition_date = p_date;

return query execute
    $dynamic$
        select *
        from data
        where slice_id between $1 and $2
    $dynamic$
    using min_slice_id,max_slice_id;

end;
$function$language plpgsql;

这将使用给定日期的适当切片范围构建查询,并在计划程序将获得检查确切分区所需的信息时在运行时进行计划.

为了使函数更通用而不失去计划程序在运行时获取信息的能力,请在过滤器中使用or参数为null构造.

create function select_from_data (
    p_date date,value_1 integer default null,value_2 integer default null
)
returns setof data as $function$

declare
    min_slice_id integer,max_slice_id
    from slice
    where partition_date = p_date;

return query execute
    $dynamic$
        select *
        from data
        where
            slice_id between $1 and $2
            and (some_col = $3 or $3 is null)
            and (another_col = $4 or $4 is null)
    $dynamic$
    using min_slice_id,max_slice_id,value_1,value_2;

end;
$function$language plpgsql;

现在,如果某个参数作为null传递,它将不会干扰查询.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!