英文:
Best index for composite primary key of int4 and date
问题
我有一个包含int4和DATE列的复合主键的单个表，因此是id和created_at。
我计划使用日期范围查询这个表，类似于这样：
SELECT *
FROM table
WHERE id = 4
AND created_at BETWEEN '2022-02-02' AND '2022-03-02';
现在我的问题是，在这种情况下，最好使用什么索引？这是一个不会分区的单个表，所有行都只能插入，不会进行任何更新。
英文:
I have a single table with a composite primary key consisting of an int4 and a DATE column so it is id, created_at.
I am planning to query this table with a date range, so something like this:
SELECT *
FROM table
WHERE id = 4
AND created_at BETWEEN '2/2/2022' AND '3/2/2022';
Now my question is what is the best index to use for this scenario? It is a single table that won't be partitioned and all of the rows are insert only, no updates will ever occur.
答案1
得分: 1
将 id 设为主列:
主键 (id, created_at)
一个经验法则是：首先考虑相等，然后考虑范围。参见：
相关链接：
另外，由于...
> 这是一个不会分区且所有行都是只插入的单表。
定期在主键索引上执行 CLUSTER 操作会很有益处。CLUSTER 根据索引顺序重写表并获取排他锁。因此，只有在有维护窗口的情况下才能选择此选项。否则，可以查看社区工具 pg_repack 或 pg_squeeze，它们可以以相同的非阻塞方式执行相同的操作。参见：
像这样重新聚簇的表从表中读取的数据页数是最小的。对于非常有选择性的查询，其中索引性能占主导地位，效果很小，但随着符合条件的行数增加而增加。(CLUSTER 和社区工具还会以原始条件重写索引。)
最后，你真的需要用 SELECT * 吗？使用索引仅扫描可以直接从索引中（大部分）提供 SELECT id, created_at - 如果你足够频繁地对表进行清理的话。
另外：始终使用推荐的 ISO 日期格式，它在任何区域设置下都是明确的。 '2022-03-02'（或 '2022-3-2'），而不是 '3/2/2022'。
英文:
Make id the leading column:
PRIMARY KEY (id, created_at)
The rule of thumb is: equality first, range later. See:
Related:
Also, since ...
> It is a single table that won't be partitioned and all of the rows are insert only, no updates will ever occur.
It will be beneficial to CLUSTER the table on the PK index from time to time. CLUSTER rewrites the table according to index order and takes an exclusive lock. So only an option if there are maintenance windows allowing that. Else, look to the community tools pg_repack or pg_squeeze, which can do the same non-blocking. See:
Postgres has to read a minimum of data pages from a table freshly clustered like that. The effect is small for very selective queries, where index performance dominates, but grows with the number of qualifying rows. (CLUSTER and the community tool also rewrite the index in pristine condition.)
Finally, do you really need SELECT *? SELECT id, created_at could be (mostly) served from the index directly with an index-only scan - if you vacuum the table enough.
Aside: always use the recommended ISO date-format, which is unambiguous with any locale setting. '2022-03-02' (or '2022-3-2'), not '3/2/2022'.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论