优化 PostgreSQL 中的查询 [英] optimize Query in PostgreSQL
问题描述
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
我如何优化这个查询..你能解释一下......
how can i optimize this query.. could you please explain...
推荐答案
重新格式化您的查询计划以使其更加清晰:
Reformatting your query plan for clarity:
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
两个部分索引可能根据您的数据分布消除 seq 扫描:
Two partial indexes might eliminate seq scans depending on your data distribution:
-- 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);
最后但并非最不重要的是,如果您从未分析过数据,则需要运行:
Last but not least if you've never analyzed your data, you need to run:
VACUUM ANALYZE;
如果完成所有操作后仍然很慢,那么除了合并您的 plain_contacts 和您的联系人表之外,您无能为力:尽管有上述索引,但获得上述查询计划意味着您的大多数/所有订阅者都是订阅了该特定列表 - 在这种情况下,上述查询计划是您获得的最快速度.
If it's still slow once all that is done, there isn't much you can do short of merging your plain_contacts and your contacts tables: getting the above query plan in spite of the above indexes means most/all of your subscribers are subscribed to that particular list -- in which case the above query plan is the fastest you'll get.
这篇关于优化 PostgreSQL 中的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!