如何通过索引加速Sequelize+PostgreSQL复杂查询？

根据你的查询语句和执行计划分析，当前数据量较小时全表扫描速度尚可，但随着数据增长，全表扫描会成为性能瓶颈。以下是针对性的索引创建建议：

1. Messages表索引 #

查询中对Messages表的过滤条件集中在fromMe=true、messageId IS NULL、status='pending'，同时需要按createdAt排序，还要关联contactId、ticketId、quotedMsgId。创建部分复合索引可以直接覆盖过滤、排序和关联需求：

CREATE INDEX idx_messages_pending_outgoing ON "Messages" 
("fromMe", "status", "messageId", "scheduleDate", "createdAt")
WHERE "fromMe" = true AND "status" = 'pending' AND "messageId" IS NULL;

另外，针对引用自身的quotedMsgId，单独创建索引加速左连接：

CREATE INDEX idx_messages_quotedmsg_ref ON "Messages" ("quotedMsgId");

2. Tickets表索引 #

Tickets表的过滤条件为tenantId=1、channel='whatsapp'、whatsappId=1、status!='closed'，创建复合索引覆盖这些条件：

CREATE INDEX idx_tickets_active_whatsapp ON "Tickets" 
("tenantId", "whatsappId", "channel", "status");

如果status!='closed'是高频过滤条件，可以加上部分索引的WHERE子句进一步缩小索引范围：

CREATE INDEX idx_tickets_active_whatsapp ON "Tickets" 
("tenantId", "whatsappId", "channel")
WHERE "status" != 'closed' AND "channel" = 'whatsapp';

3. Contacts表索引 #

Contacts表的过滤条件是tenantId=1且number不为空或'null'，创建部分复合索引加速关联和过滤：

CREATE INDEX idx_contacts_valid_tenant ON "Contacts" 
("tenantId", "number")
WHERE "number" NOT IN ('', 'null');

补充说明 #

• 这些索引都是为了让数据库直接通过索引筛选数据，避免全表扫描，同时复合索引可以减少回表查询的次数。
• 若使用Sequelize，可以通过addIndex方法创建这些索引，例如Messages表的索引：

await queryInterface.addIndex('Messages', 
  ['fromMe', 'status', 'messageId', 'scheduleDate', 'createdAt'],
  {
    where: {
      fromMe: true,
      status: 'pending',
      messageId: null
    },
    name: 'idx_messages_pending_outgoing'
  }
);

内容的提问来源于stack exchange，提问作者Luiz Alves