CODEWITHSUNDEEP
pythonperformancepostgresqlodoo
ChristophLSA
ChristophLSA

Reputation: 175

Improve SQL statement with Index Scan Backward

We are using the python software Odoo which uses an ORM for the database requests (PostgreSQL). Currently I'm trying to reduce the execution times of some SQL statements. I could increase the time by adding some indexes.

Now I have one query which takes much time and can't decrease the time of execution. Can you help me to improve the speed of this query? I can't change the query itself (maybe only some small parts) but maybe we can change something on the table structure itself?

The query:

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;

Here is the output of EXPLAIN ANALYZE:

# 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)

You can watch the explanation on http://explain.depesz.com/s/cbG

Just for the note - the number of rows:

          table           | num_rows 
--------------------------+----------
 public.mail_notification |    42254
 public.mail_message      |   294474
 public.purchase_order    |     6566
(3 rows)

And here is the output of \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

Upvotes: 0

Views: 1018

Answers (1)

Tasos Vogiatzoglou
Tasos Vogiatzoglou

Reputation: 2453

You can create a selective index for the specific query, something like

CREATE INDEX idx_order_messages on mail_message (id) where model = 'purchase.order'

A smaller index results in fewer reads etc, so it must be faster.

Upvotes: 1

Related Questions