查询未命中索引-这些是要索引的正确列吗？ [英] Query does not hit the index - are these the proper columns to index?

问题描述

以下对Postgres数据库的查询有时速度很慢（4,000 + ms）：

The following query to a Postgres database is quite slow at times (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并分析查询时，结果如下：

When I run psql and analyze the query, this is the result:

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"

但是分析方法并没有达到索引。我会输入错误的列吗？

But the analyze method isn't hitting the index. Am I including the wrong columns?

推荐答案

id 应该出现最后。像

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

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

Generally: equality first, range later. Related:

• 多列索引和效果

• Multicolumn index and performance

id 仍应附加，除非有（发件人，到， SmsMessageSid）的每行几行。这样，Postgres可以直接从索引顶部选择第一行-在索引扫描中，而不是像现在看到的 bitmap索引扫描。

id should still be appended, unless there are very few rows per combination of ("From", "To", "SmsMessageSid"). This way, Postgres can pick the first row from the top of the index directly - in an index scan, not a bitmap index scan like you see now.

相关：

• 当索引扫描是更好的选择时，Postgres不使用索引


• 如何在PostgreSQL中使DISTINCT更快打开？

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

这篇关于查询未命中索引-这些是要索引的正确列吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！