ruby-on-rails - 查询未命中索引 - 这些列是否适合索引？

Question

以下对 Postgres 数据库的查询有时非常慢(4,000+ms):

EXPLAIN ANALYZE SELECT "sms".* FROM "sms" WHERE "sms"."From" = 'NUMBER1' AND "sms"."To" = 'NUMBER2' AND "sms"."SmsMessageSid" = 'UNIQUE_ID' ORDER BY "sms"."id" ASC LIMIT 1;

当我运行 psql 并分析查询时，结果如下:

Limit  (cost=5045.12..5045.12 rows=1 width=609) (actual time=57.011..57.011 rows=0 loops=1)
   ->  Sort  (cost=5045.12..5045.12 rows=1 width=609) (actual time=57.009..57.009 rows=0 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on sms  (cost=46.02..5045.11 rows=1 width=609) (actual time=56.993..56.993 rows=0 loops=1)
               Recheck Cond: (("To")::text = 'NUMBER1'::text)
               Filter: ((("From")::text = 'NUMBER2'::text) AND (("SmsMessageSid")::text = 'UNIQUE_ID'::text))
               Rows Removed by Filter: 2501
               Heap Blocks: exact=1230
               ->  Bitmap Index Scan on "index_sms_on_To"  (cost=0.00..46.02 rows=2623 width=0) (actual time=0.345..0.345 rows=2566 loops=1)
                     Index Cond: (("To")::text = 'NUMBER1'::text)

我创建了一个索引如下:

add_index "sms", ["id", "From", "To", "SmsMessageSid"], name: "on_id_from_to_sms_message_sid"

但是分析方法没有达到索引。我是否包含了错误的列？

Answer 1

id 应该在最后。喜欢

add_index "sms", ["From", "To", "SmsMessageSid", "id"], name: "on_id_from_to_sms_message_sid"

通常:首先是相等，然后是范围。相关:

• Multicolumn index and performance

id 仍应附加，除非每个 ("From", "To", "SmsMessageSid") 组合的行很少。这样，Postgres 可以直接从索引的顶部选取第一行 - 在索引扫描中，而不是像您现在看到的位图索引扫描。

相关:

• Postgres not using index when index scan is much better option
• How to make DISTINCT ON faster in PostgreSQL?