我们正在使用Python软件ODOO，该软件使用ORM用于数据库请求（PostgreSQL）。目前，我正在尝试减少某些SQL语句的执行时间。我可以通过添加一些索引来增加时间。
现在，我有一个查询需要很多时间，并且不能减少执行时间。您能帮我提高此查询的速度吗？我无法更改查询本身（也许只有一些小部分），但也许我们可以在表结构上更改某些内容？
查询：
SELECT "purchase_order".id
FROM "mail_message" AS "purchase_order__message_ids",
"mail_notification" AS "purchase_order__message_ids__notification_ids",
"purchase_order"
WHERE ("purchase_order"."id"="purchase_order__message_ids"."res_id"
AND "purchase_order__message_ids"."id"="purchase_order__message_ids__notification_ids"."message_id")
AND ((("purchase_order"."state" NOT IN ('draft',
'sent',
'bid',
'confirmed'))
OR "purchase_order"."state" IS NULL)
AND (("purchase_order__message_ids"."model" = 'purchase.order')
AND (("purchase_order__message_ids__notification_ids"."partner_id" IN (3))
AND ("purchase_order__message_ids__notification_ids"."is_read" IS NULL
OR "purchase_order__message_ids__notification_ids"."is_read" = FALSE))))
ORDER BY "purchase_order"."id" DESC LIMIT 100;
这是解释分析的输出：
# EXPLAIN ANALYZE SELECT "purchase_order".id FROM "mail_message" as "purchase_order__message_ids","mail_notification" as "purchase_order__message_ids__notification_ids","purchase_order" WHERE ("purchase_order"."id"="purchase_order__message_ids"."res_id" AND "purchase_order__message_ids"."id"="purchase_order__message_ids__notification_ids"."message_id") AND ((("purchase_order"."state" not in ('draft','sent','bid','confirmed')) OR "purchase_order"."state" IS NULL) AND (("purchase_order__message_ids"."model" = 'purchase.order') AND (("purchase_order__message_ids__notification_ids"."partner_id" in (3)) AND ("purchase_order__message_ids__notification_ids"."is_read" IS NULL or "purchase_order__message_ids__notification_ids"."is_read" = false )))) ORDER BY "purchase_order"."id" DESC limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.99..10720.11 rows=100 width=4) (actual time=956.615..958.683 rows=2 loops=1)
-> Nested Loop (cost=0.99..63779.73 rows=595 width=4) (actual time=956.609..958.669 rows=2 loops=1)
-> Nested Loop (cost=0.71..63177.50 rows=1325 width=4) (actual time=956.573..958.609 rows=2 loops=1)
-> Index Scan Backward using mail_message_res_id_index on mail_message purchase_order__message_ids (cost=0.42..48385.44 rows=40025 width=8) (actual time=110.607..518.171 rows=40149 loops=1)
Filter: ((model)::text = 'purchase.order'::text)
Rows Removed by Filter: 254269
-> Index Scan using mail_notification_message_id_index on mail_notification purchase_order__message_ids__notification_ids (cost=0.29..0.36 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=40149)
Index Cond: (message_id = purchase_order__message_ids.id)
Filter: (((is_read IS NULL) OR (NOT is_read)) AND (partner_id = 3))
Rows Removed by Filter: 0
-> Index Scan using purchase_order_pkey on purchase_order (cost=0.28..0.44 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=2)
Index Cond: (id = purchase_order__message_ids.res_id)
Filter: (((state)::text <> ALL ('{draft,sent,bid,confirmed}'::text[])) OR (state IS NULL))
Planning time: 2.468 ms
Execution time: 958.792 ms
(15 rows)
您可以在http://explain.depesz.com/s/cbg
上观看说明仅用于注释 - 行的数量：
table | num_rows
--------------------------+----------
public.mail_notification | 42254
public.mail_message | 294474
public.purchase_order | 6566
(3 rows)
这是d mail_message的输出：
# d mail_message
Table "public.mail_message"
Column | Type | Modifiers
----------------+-----------------------------+-----------------------------------------------------------
id | integer | not null default nextval('mail_message_id_seq'::regclass)
create_uid | integer |
create_date | timestamp without time zone |
write_date | timestamp without time zone |
write_uid | integer |
body | text |
model | character varying(128) |
record_name | character varying |
date | timestamp without time zone |
subject | character varying |
message_id | character varying |
parent_id | integer |
res_id | integer |
subtype_id | integer |
author_id | integer |
type | character varying |
email_from | character varying |
mail_server_id | integer |
no_auto_thread | boolean |
reply_to | character varying |
Indexes:
"mail_message_pkey" PRIMARY KEY, btree (id)
"mail_message_author_id_index" btree (author_id)
"mail_message_message_id_index" btree (message_id)
"mail_message_model_index" btree (model)
"mail_message_model_res_id_idx" btree (model, res_id)
"mail_message_parent_id_index" btree (parent_id)
"mail_message_res_id_index" btree (res_id)
"mail_message_subtype_id_index" btree (subtype_id)
Foreign-key constraints:
"mail_message_author_id_fkey" FOREIGN KEY (author_id) REFERENCES res_partner(id) ON DELETE SET NULL
"mail_message_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
"mail_message_mail_server_id_fkey" FOREIGN KEY (mail_server_id) REFERENCES ir_mail_server(id) ON DELETE SET NULL
"mail_message_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES mail_message(id) ON DELETE SET NULL
"mail_message_subtype_id_fkey" FOREIGN KEY (subtype_id) REFERENCES mail_message_subtype(id) ON DELETE SET NULL
"mail_message_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL
Referenced by:
TABLE "crm_lead_forward_to_partner" CONSTRAINT "crm_lead_forward_to_partner_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES mail_message(id) ON DELETE SET NULL
TABLE "mail_compose_message" CONSTRAINT "mail_compose_message_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES mail_message(id) ON DELETE SET NULL
TABLE "mail_mail" CONSTRAINT "mail_mail_mail_message_id_fkey" FOREIGN KEY (mail_message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "mail_message" CONSTRAINT "mail_message_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES mail_message(id) ON DELETE SET NULL
TABLE "mail_message_res_partner_rel" CONSTRAINT "mail_message_res_partner_rel_mail_message_id_fkey" FOREIGN KEY (mail_message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "mail_notification_bcc" CONSTRAINT "mail_notification_bcc_message_id_fkey" FOREIGN KEY (message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "mail_notification_cc" CONSTRAINT "mail_notification_cc_message_id_fkey" FOREIGN KEY (message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "mail_notification" CONSTRAINT "mail_notification_message_id_fkey" FOREIGN KEY (message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "mail_vote" CONSTRAINT "mail_vote_message_id_fkey" FOREIGN KEY (message_id) REFERENCES mail_message(id) ON DELETE CASCADE
TABLE "message_attachment_rel" CONSTRAINT "message_attachment_rel_message_id_fkey" FOREIGN KEY (message_id) REFERENCES mail_message(id) ON DELETE CASCADE
您可以为特定查询创建选择性索引，例如
CREATE INDEX idx_order_messages on mail_message (id) where model = 'purchase.order'
较小的索引导致读取等少，因此必须更快。