Postgres中索引列的查詢非常慢

我對索引列的查詢非常慢。給定查詢

SELECT * 
FROM orders 
WHERE shop_id = 3828 
ORDER BY updated_at desc 
LIMIT 1

explain analyze回來：

   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.43..594.45 rows=1 width=175) (actual time=202106.830..202106.831 rows=1 loops=1)
  ->  Index Scan Backward using index_orders_on_updated_at on orders  (cost=0.43..267901.54 rows=451 width=175) (actual time=202106.827..202106.827 rows=1 loops=1)
        Filter: (shop_id = 3828)
        Rows Removed by Filter: 1604818
Planning time: 98.579 ms
Execution time: 202127.514 ms
(6 rows)

表說明為：

                                        Table "public.orders"
      Column       |            Type             |                           Modifiers
--------------------+-----------------------------+---------------------------------------------------------------
id                 | integer                     | not null default nextval('orders_id_seq'::regclass)
sent               | boolean                     | default false
created_at         | timestamp without time zone |
updated_at         | timestamp without time zone |
name               | character varying(255)      |
shop_id            | integer                     |
recovered_at       | timestamp without time zone |
total_price        | double precision            |
Indexes:
   "orders_pkey" PRIMARY KEY, btree (id)
   "index_orders_on_recovered_at" btree (recovered_at)
   "index_orders_on_shop_id" btree (shop_id)
   "index_orders_on__updated_at" btree (updated_at)

它是一個 Postgres 伺服器，在 AWS RDS t2 微型實例上執行。

該表有大約 260 萬行。

我不太了解 Postgresql，但是您正在檢查兩個單獨的鍵以找到您要查找的值，請嘗試將其創建為複合鍵

"index_orders_on_shop_id" btree (shop_id)
"index_orders_on__updated_at" btree (updated_at)

變成

"index_orders_on_shop_id__updated_at" btree (shop_id,updated_at)

這可能會有所幫助

如果有一種方法可以更好地在索引中包含值

ORDER BY您的條款中隱藏著一個微妙的問題：

ORDER BY updated_at DESC 

將首先對 NULL 值進行排序。我假設你不希望那樣。您的列updated_at可以為 NULL（缺少NOT NULL約束）。可能應該添加缺少的約束。您的查詢應該以任何一種方式修復：

SELECT * 
FROM   orders 
WHERE  shop_id = 3828 
ORDER  BY updated_at **DESC NULLS LAST**
LIMIT  1;

提到的多列索引@Ste Bov應該相應地進行調整：

CREATE INDEX orders_shop_id_updated_at_idx ON orders (shop_id, updated_at **DESC NULLS LAST**);

然後你得到一個 basicIndex Scan而不是 (幾乎一樣快) Index Scan Backward，你不會得到一個額外的index condition: Index Cond: (updated_at IS NOT NULL).

有關的：

• 日期查詢範圍內未使用的索引
• 優化對一系列時間戳（兩列）的查詢

旁白

您可以通過優化大表的列序列來節省一些浪費的磁碟空間（這會使一切變得更快）：

id                 | integer                     | not null default nextval( ...
shop_id            | integer                     |
sent               | boolean                     | default false
name               | varchar(255)                |
total_price        | double precision            |
recovered_at       | timestamp without time zone |
created_at         | timestamp without time zone |
updated_at         | timestamp without time zone |

看：

• 為讀取性能配置 PostgreSQL

NOT NULL向所有不能為 NULL 的列添加約束。

考慮textorvarchar而不是varchar(255)，timestamptz而不是timestampandinteger價格（以美分錶示）或numeric（對於小數），它是一種任意精度類型，並完全按照給定的方式儲存您的值。切勿將有損浮點類型用於“價格”或與金錢有關的任何事情。

引用自：https://dba.stackexchange.com/questions/112810