postgresql - 这个 PostgreSQL 查询应该使用索引吗？

Question

我有两个表:

CREATE TABLE soils (
    sample_id       TEXT PRIMARY KEY, 
    project_id      TEXT, 
    technician_id   TEXT
);
CREATE INDEX soils_idx
ON soils
USING btree
(sample_id COLLATE pg_catalog."default");

CREATE TABLE assays (
    sample_id   TEXT PRIMARY KEY, 
    mo_ppm      NUMERIC
    );
CREATE INDEX assays_idx
ON assays
USING btree
(sample_id COLLATE pg_catalog."default");

每个表包含大约 50 万条记录，实际上，每个表包含大约 20 个 TEXT 类型的额外列(为了节省时间，在上面发布的 DDL 中省略了)。

当我执行查询时:

EXPLAIN SELECT
   s.sample_id, s.project_id, s.technician_id, a.mo_ppm
FROM
   soils AS s INNER JOIN assays AS a ON s.sample_id = a.sample_id

我得到 2 个 SEQ SCAN，而不是对索引的查找。这是预期的行为吗？

Answer 1

由于您没有WHERE 条件，您可以有效地读取整个表。运行顺序扫描并且根本不涉及任何索引的成本更低。

尝试:

EXPLAIN
SELECT s.sample_id, s.project_id, s.technician_id, a.mo_ppm
FROM   soils s
JOIN   assays a USING (sample_id)
WHERE  <some condition that returns few rows>;

... 并且应使用与 WHERE 条件匹配的索引。

您不需要在 PRIMARY KEY 列上定义索引。 PK 约束自动使用唯一索引实现。您的附加索引是多余的，没有用。

外键列上的索引是个好主意，但您的示例中没有，这看起来很奇怪。就像两个表可以合并为一个。可能只是对测试用例的过度简化。

最后，对于大表，我会考虑使用简单的 integer 主键而不是 text，可能是 serial柱子。这通常更快。