主页 / dba / 问题 / 162582
Accepted
Dave
Asked: 2017-01-30 13:24:39 +0800 CST

有没有一种更有效的方法来搜索记录，但前提是它有关联？

  • 772

我正在使用 Postgres 9.5。我想选择一些记录，但前提是它们在另一张表中至少有一个关联。我想出了这个查询：

select distinct m.name, m.id 
from machines m, parts rt 
where m.id = rt.machine_id 
  and m.name like '%Name%';

但我认为这不是特别有效，因为如果有 50000 个关联，我认为它们都被加载然后“区分”通过，这似乎有点低效。有没有一种更有效的方法来检查加载记录，但前提是它有一个或多个关联？

postgresql performance

1 个回答

  1. Best Answer

    您的查询结构方式，您可以使用EXISTS表达式编写它：

    SELECT
    m.name, m.id 
FROM
    machines m
WHERE
    m.name LIKE '%Name%'
    AND EXISTS (SELECT * 
                  FROM parts rt
                 WHERE rt.machine_id = m.id
               ) ;

    如果您希望查询高效，请考虑到最困难的部分可能是检查机器name。考虑machines.name使用trigram index. Trigram 索引允许快速查找LIKE '%something%'条件。

    CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx 
    ON machines 
    USING GIST (name gist_trgm_ops);

    最有可能的是，数据库规划器将制定一个执行计划，首先找到nameLIKE '%Name%' 的机器（使用name_like_idx索引），然后，对于该子集中的每台机器，它将检查是否有与他们; 如果是这样，它们将成为结果的一部分。

    为了提高效率，还假定 上也有一个索引parts.machine_id，因为这将是 PostgreSQL 将如何有效地检查该部分是否存在的方式。如果machine_id是外键，总是建议对其进行索引。

    这个查询不需要DISTINCT（假设ids 已经是唯一的）。

    • 2

相关问题