PostgreSQL中如何优化查询

Question

PostgreSQL中如何优化查询

3

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
WHERE plain_contacts.has_email 
      AND NOT contacts.email_bad 
      AND NOT contacts.email_unsub 
      AND contacts_lists.list_id =67339

如何优化这个查询...你能解释一下吗？

- Rafiu

1

你在contacts_lists.list_id上有索引吗？你也可以尝试在contacts.email_bad（或email_unsub）上建立索引。请展示查询的执行计划（最好使用EXPLAIN ANALYZE）。 - user330315

1

你对那个查询有什么问题？ - Endy Tjahjono

1

每当你在 PostgreSQL 查询方面遇到问题时，请发布出现问题的查询的 PostgreSQL 版本和 EXPLAIN ANALYZE。如果没有这些信息，任何回复都是纯粹的猜测。 - intgr

查询计划聚合（成本=126377.96..126377.97行=1宽度=0） ->哈希连接（成本=6014.51..126225.38行=61033宽度=0）哈希条件：（contacts_lists.contact_id = plain_contacts.contact_id） ->哈希连接（成本=3067.30..121828.63行=61033宽度=8）哈希条件：（contacts_lists.contact_id = contacts.id） ->使用index_contacts_lists_on_list_id_and_contact_id索引扫描contacts_lists（成本=0.00..116909.97行=61033宽度=4） - Rafiu

以上两个注释是我的查询计划，请验证以优化查询。 - Rafiu

显示剩余3条评论

5个回答

4

这已经是一个非常简单的查询，只要统计数据是最新的，数据库就会以最有效的方式运行它。

因此，在查询本身方面，没有太多要做的。

在数据库管理方面，您可以添加索引-数据库中应该有所有连接条件的索引，还应该有where子句（plain_contacts和contacts_lists中的list_id、contact_id作为FK）中最具选择性的部分的索引。这是提高此查询性能的最重要机会（数量级）。仍然如SpliFF所指出的那样，您可能已经拥有这些索引，请检查。

此外，Postgres有很好的explain命令，您应该学习并使用它。它将有助于优化查询。

- Unreason

我已经将这两个作为索引了。还有其他优化此查询的方法吗？如果有，请解释一下。 - Rafiu

你是理论上感兴趣还是需要提高查询速度？ - Unreason

SELECT count(contacts_lists.contact_id) FROM contacts_lists WHERE contacts_lists.list_id =66996 AND contacts_lists.contact_id IN (SELECT id FROM contacts WHERE (NOT contacts.email_bad AND NOT contacts.email_unsub) AND id IN (SELECT plain_contacts.contact_id FROM plain_contacts WHERE plain_contacts.has_email))) - Rafiu

现在我已经修改了查询语句，您能否解释哪一个是最好的？ - Rafiu

1

由于您只想包含具有连接表中设置了某些标志的行，因此我建议将该语句移动到联接子句中：

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
          AND NOT plain_contacts.has_email
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
          AND NOT contacts.email_unsub 
          AND NOT contacts.email_bad 
WHERE contacts_lists.list_id =67339

我不确定这是否会对性能产生巨大影响，但值得一试。最好为连接的表也建立索引以获得最佳性能，像这样：

plain_contacts: contact_id, has_email
contacts: id, email_unsub, email_bad

- jishi

感谢您的回复。但是它给出了与之前相同的结果。 - Rafiu

SELECT count(contacts_lists.contact_id) FROM contacts_lists WHERE contacts_lists.list_id =66996 AND contacts_lists.contact_id IN (SELECT id FROM contacts WHERE (NOT contacts.email_bad AND NOT contacts.email_unsub) AND id IN (SELECT plain_contacts.contact_id FROM plain_contacts WHERE plain_contacts.has_email))) - Rafiu

现在我已经修改了查询语句，您能否解释哪一个是最好的？ - Rafiu

这完全取决于你拥有哪些索引。最好的那个将是最快的，就这么简单。你版本的优点是，如果你有（例如，仅在email_bad或email_unsub上建立的索引）可以更好地利用没有id列的索引。 - jishi

感谢您的友好回复。 - Rafiu

1

最近在数据库上运行了ANALYZE吗？EXPLAIN计划中的行数是否看起来合理？（看起来你只运行了EXPLAIN。 EXPLAIN ANALYZE可以同时提供估计和实际的时间。）

- Andrew Lazarus

0

你可以使用 SELECT count(1) ...，但除此之外，我认为它看起来很好。如果你真的遇到困难，你可以通过视图缓存查询的某些部分或在 contact_id 和 list_id 上放置索引（我假设你已经在 id 上有一个索引）。

- SpliFF

еЬ®count(*)еТМcount(1)дєЛйЧіж†єжЬђж≤°жЬЙдїїдљХжАІиГљеЈЃеЉВгАВ - user330315

哪个版本？你能证明吗？我怀疑是否曾有过差异。 - user330315

我在Postgres 7.3的用户注释中看到过这个。这并不意味着它是正确的，可能只是一个很久以前的假设。 - SpliFF

1

7.x已经不再是一个参考标准了。实际上，任何基于8.1版本之前的（性能）信息都已经过时了。 - user330315

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Denis de Bernardy · Accepted Answer

为了更清晰地重构查询计划：

QUERY PLAN Aggregate (cost=126377.96..126377.97 rows=1 width=0)
  -> Hash Join (cost=6014.51..126225.38 rows=61033 width=0)
     Hash Cond: (contacts_lists.contact_id = plain_contacts.contact_id)
    -> Hash Join (cost=3067.30..121828.63 rows=61033 width=8)
       Hash Cond: (contacts_lists.contact_id = contacts.id)
      -> Index Scan using index_contacts_lists_on_list_id_and_contact_id
         on contacts_lists (cost=0.00..116909.97 rows=61033 width=4)
         Index Cond: (list_id = 66996)
         -> Hash (cost=1721.41..1721.41 rows=84551 width=4)
         -> Seq Scan on contacts (cost=0.00..1721.41 rows=84551 width=4)
            Filter: ((NOT email_bad) AND (NOT email_unsub))
            -> Hash (cost=2474.97..2474.97 rows=37779 width=4)
            -> Seq Scan on plain_contacts (cost=0.00..2474.97 rows=37779 width=4)
               Filter: has_email

如果你的数据分布情况适合，两个部分索引可能会消除序列扫描：

-- if many contacts have bad emails or are unsubscribed:
CREATE INDEX contacts_valid_email_idx ON contacts (id)
WHERE (NOT email_bad AND NOT email_unsub);

-- if many contacts have no email:
CREATE INDEX plain_contacts_valid_email_idx ON plain_contacts (id)
WHERE (has_email);

你可能在外键上缺少索引：

CREATE INDEX plain_contacts_contact_id_idx ON plain_contacts (contact_id);

最后，如果你从未分析过你的数据，你需要运行以下代码：

VACUUM ANALYZE;

如果在完成所有这些操作后仍然很慢，除了合并你的plain_contacts和contacts表之外，你几乎没有什么可以做的了：尽管有以上的索引，但得到上述查询计划意味着大多数/全部订阅者都订阅了该特定列表--在这种情况下，以上查询计划是最快的。