BRIN 索引在数据在物理存储中捆绑在一起的表上效果最好（或仅）。
除了测试或考虑创建数据的逻辑之外，有没有办法提前检查这一点？
就我而言，我怀疑我的表是根据客户端 ID 排序的，但我不确定这是否足够一致。
我正在 AWS RDS PostgreSQL 实例上运行数据仓库。大多数繁重的工作都是在夜间批处理期间完成的，并且我们经常使用截断重建策略，对于大型表（100M 行）也是如此。
这似乎会导致 autovacuum 问题，在夜间批次 2 到 4 之间，前 10 个 SQL 语句中的 7 个是针对大型表的 VACUUM ANALYZE 语句：
它们占用了我的 RDS 系统
并将字节平衡降至 0，之后机器速度大大减慢：
显然，将 VACUUM ANALYZE 推迟到稍后的时间范围（当机器几乎空闲时）会更明智。
阅读一些文档后，我可以想到两种策略来完成此任务：
- 关闭这些大表的 Autovacuum，并安排一个稍后运行 Vacuum 的进程
- 将 设为
autovacuum_cost_delay一个合理的值。因为这通常会推迟 Vacuum 处理（或者可能仅针对这些表将其设置为合理的值）。
然而，在这种情况下，什么是合理的值呢？我读到默认值为 2 毫秒。200ms 应该大多少？10秒？1分钟？60分钟？
我正在寻找一个合理的值来开始测试或其他可以帮助我的建议。
注意。该机器为 2cpu、16GB m6g.large，这些是 autovauccum 相关参数的当前设置：
额外信息
@jjanes是的，它几乎没有达到0的字节平衡，但是，这是我实际上设法实现的一个仔细的平衡。我经常遇到这样的情况：实际上跌到0后需要很长时间才能恢复。例子
然后我的机器也开始增加读/写延迟和 DiskQueueDepth
夜间批量加载涉及很多表（目前约900张），这些表都是在23点到6点30分之间通过作业加载/转换的，最繁忙的时间是2点到4点。许多桌子都很小，只有少数桌子相当大。
@jjanes & @frank-heikens，哪个后续版本确实有所不同？这是从 14 迁移到 15 之前和之后的情况的两张截图： 之前： 之后
：
更新实施了@Laurence Albe 的建议。观察结果：
字节平衡更好：
数据库负载更加均匀：
我喜欢使用 CTE 来创建漂亮且清晰的查询。但是，我很确定我创建的查询效率确实很低。
有没有更好的方法来做到这一点并使事情变得清晰？
with first_date as (
-- selecting just 1 date
-- namely: 1 full year ago
select (extract(year from current_date - interval '1 year')||'-01-01' )::date as date
)
, last_date as (
select date from star.dim_date where current_cal_day='Current'
)
, total_active_customers_ps_day as(
select
dd.date
, dd.is_first_day_in_month
, dd.is_last_day_in_month
, count(dc.id) as total_customers
from first_date, last_date,
star.dim_date dd
-- join with dim_client, using first_subscription_start_date & last_subscription_end_date
-- to get the ids of just the active clients
join star.dim_client dc on dd.date
between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01')
and dc.created <= dd.date
and dc.first_subscription_start_date >= dc.created::date
where
dd.date >= first_date.date
and dd.date <= last_date.date
group by
dd.date
, dd.is_first_day_in_month
, dd.is_last_day_in_month
)
select * from total_active_customers_ps_day ;
我认为我引起了一些笛卡尔连接，因为这个查询效率更高
with total_active_customers_ps_day as(
select
dd.date
, dd.is_first_day_in_month
, dd.is_last_day_in_month
, count(dc.id) as total_customers
from
star.dim_date dd
-- join with dim_client, using first_subscription_start_date & last_subscription_end_date
-- to get the ids of just the active clients
join star.dim_client dc on dd.date
between dc.first_subscription_start_date and coalesce(dc.last_subscription_end_date::date, '3000-01-01')
and dc.created <= dd.date
and dc.first_subscription_start_date >= dc.created::date
where
dd.date >= (extract(year from current_date - interval '1 year')||'-01-01' )::date
and dd.date <= (select date from star.dim_date where current_cal_day='Current')
group by
dd.date
, dd.is_first_day_in_month
, dd.is_last_day_in_month
)
select * from total_active_customers_ps_day ;
有什么更好的方法来做到这一点？
我在键上有一个大表连接。explain plan可以看出大部分时间花在了排序上。
我能做些什么来防止大的排序步骤吗？我可以预先对表格进行排序吗？
在这里查询和计划：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");
我在不使用索引的键上有一个大型表连接。
在这里查询和计划： 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");
但未使用索引（请参阅查询计划）。
有任何线索说明为什么这种情况没有发生吗？最大的步骤是排序，我可以对表进行预排序来防止这种情况发生吗？
我想将逗号分隔的 ID 列表与查询结果进行比较，如下所示：
select * from (values (2201322723),
(2201318904),
(2201299380),
(2164969641),
(5682476934),
(2201314720),
) as t(object_id)
EXCEPT
select object_id from star.dim_partner where entity = 'partners' and is_active is true;
上面的工作，然而，它需要我使用一个支持基于列的文本编辑器来添加圆括号（这个列表实际上有点长）。
是否有更简单/更好的方法可以使用 PostgreSQL 将逗号分隔列表转换为（临时）表？
有一点像：
select item as object_id from ARRAY[6058271246, 2201299240, 1801904030, 2097401903];
?
这将允许我在方括号之间剪切和粘贴任何 ID 列表。
我有使用子查询 ( select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) 来查找表的最小和最大日期的查询。
似乎子查询经常重复。
- 有没有办法在没有 CTE的情况下重写查询，这样子查询就不会在每个循环中执行
- 另一种加快查询速度的方法是使用生成系列，而不是从带有日期的表中选择日期。任何线索是否会有所作为？
询问：
SELECT
dim_date.date AS date
, stage_net_subscription_020_classes.client_id AS client_id
, stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
where date >= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
and date <= (select max(date) from stage.stage_net_subscription_010_filter_and_net_pds);
