Question 306180 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Query Optimization: Avoiding Sequential Scan on Messages Table

Problem:

I have a PostgreSQL query that retrieves messages between two users from a "Messages" table. However, the query is performing a sequential scan on the table, and I’m looking for ways to optimize it.

explain SELECT t."Id", t."Content", t."ConversationId", t."CreatedAt", t."IsReadByRecipient", t."ReadAt", t."RecipientId", t."SenderId"
FROM (
    SELECT m."Id", m."Content", m."ConversationId", m."CreatedAt", m."IsReadByRecipient", m."ReadAt", m."RecipientId", m."SenderId"
    FROM "Messages" AS m
    WHERE (m."SenderId" = 15 AND m."RecipientId" = 1) OR (m."RecipientId" = 15 AND m."SenderId" = 1)
    LIMIT 100
) AS t
ORDER BY t."CreatedAt";
";

I currently have the following indexes on the "Messages" table:

PK_Messages
IX_Messages_CreatedAt
IX_Messages_RecipientId
IX_Messages_SenderId

Additional Information:

The table has a significant number of rows.
I’ve already tried creating a composite index on ("SenderId", "RecipientId", "CreatedAt") but didn’t see the desired improvement.

my query plan is like this :

Sort  (cost=194.13..194.38 rows=100 width=88)
"  Sort Key: m.""CreatedAt"""
  ->  Limit  (cost=0.00..190.80 rows=100 width=88)
"        ->  Seq Scan on ""Messages"" m  (cost=0.00..36001.04 rows=18868 width=88)"
"              Filter: (((""SenderId"" = 15) AND (""RecipientId"" = 1)) OR ((""RecipientId"" = 15) AND (""SenderId"" = 1)))"

What other index strategies can I try to optimize this query and avoid the sequential scan?
Are there any other factors that might be contributing to the suboptimal performance?

Any guidance or suggestions would be greatly appreciated. Thank you!

Tags:

2

Answers


  1. 0

    Create an index on ("SenderId", "RecipientID") and rewrite the query to

    ... WHERE ("SenderId", "RecipientId") = ANY(ARRAY[(15,1), (1,15)])
    Login or Signup to reply.
  2. 0

    You haven’t shown any suboptimal performance. For all we know, that is actually the fastest way to do the query.

    You could canonicalize the order of the ids and search on that canonical representation. Something like this (ignoring your quoting which makes my eyes bleed):

    create index on messages (least(recipient_id,sender_id), greatest(recipient_id, sender_id));

...where least(recipient_id,sender_id)=1 and greatest(recipient_id, sender_id)=15
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search