Postgres 不夠聰明，無法將 WHERE 和 ORDER BY 中的欄位組合起來進行索引掃描？

我在 Postgres 9.6 中有下表：

                                      Table "public.TagNotifications"
   Column    |            Type             |                            Modifiers                            
--------------+-----------------------------+-----------------------------------------------------------------
createdAt    | timestamp with time zone    | not null default now()
updatedAt    | timestamp with time zone    | not null default now()
id           | integer                     | not null default nextval('"TagNotifications_id_seq"'::regclass)
tag          | character varying(255)      | not null
triggerId    | integer                     | 
userId       | integer                     | not null
comparison   | "TagNotificationComparison" | 
setpoint     | double precision            | 
severity     | "TagNotificationSeverity"   | 
acknowledged | boolean                     | not null default false
value        | double precision            | 
Indexes:
   "TagNotifications_pkey" PRIMARY KEY, btree (id)
   "TagNotificactions_userId_acknowledged_createdAt_tag_id" btree ("userId", acknowledged, "createdAt" DESC, tag, id)
   "TagNotificactions_userId_acknowledged_tag_createdAt_id" btree ("userId", acknowledged, tag, "createdAt" DESC, id)
Foreign-key constraints:
   "TagNotifications_tag_fkey" FOREIGN KEY (tag) REFERENCES "Metadata"(tag) ON UPDATE CASCADE ON DELETE CASCADE
   "TagNotifications_triggerId_fkey" FOREIGN KEY ("triggerId") REFERENCES "TagNotificationTriggers"(id) ON UPDATE CASCADE ON DELETE SET NULL
   "TagNotifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE

我正在嘗試確保以下查詢（以及基於游標分頁的類似查詢）的良好性能：

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                    QUERY PLAN                                     
------------------------------------------------------------------------------------
Limit  (cost=840.10..840.12 rows=6 width=75)
  ->  Sort  (cost=840.10..856.04 rows=6376 width=75)
        Sort Key: tag, "createdAt" DESC, id
        ->  Seq Scan on "TagNotifications"  (cost=0.00..725.81 rows=6376 width=75)
              Filter: ((NOT acknowledged) AND ("userId" = 2))
(5 rows)

在這種情況下，為什麼 Postgres 不夠聰明，無法使用索引？如果我包含"userId"and acknowledgedin ORDER BY，它會使用索引，但由於以下條件，這不應該是必需的WHERE：

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "userId" ASC, "acknowledged" ASC, "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                                                  QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.41..2.27 rows=6 width=75)
  ->  Index Scan using "TagNotificactions_userId_acknowledged_tag_createdAt_id" on "TagNotifications"  (cost=0.41..1974.27 rows=6376 width=75)
        Index Cond: (("userId" = 2) AND (acknowledged = false))
        Filter: (NOT acknowledged)
(4 rows)

它在 v10 中變得更加智能。

我認為這裡描述了它之前不夠聰明的原因：

https://www.postgresql.org/message-id/1788.1481605684@sss.pgh.pa.us

我不知道如何總結，所以我只會說“索引中的布爾值令人困惑”

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