patternsqlMinor
Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?
Viewed 0 times
orderscancombinepostgresfieldswheresmartforandindex
Problem
I have the following table in Postgres 9.6:
I'm trying to ensure good performance for the following query (and similar queries with cursor-based pagination):
```
EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
QUERY PLAN
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 CASCADEI'm trying to ensure good performance for the following query (and similar queries with cursor-based pagination):
```
EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
QUERY PLAN
Solution
It has grown smarter in v10.
I think the reason it wasn't smart enough before is described here:
https://www.postgresql.org/message-id/1788.1481605684@sss.pgh.pa.us
I don't know how to summarize that, so I'll just say "booleans in indexes are confusing"
I think the reason it wasn't smart enough before is described here:
https://www.postgresql.org/message-id/1788.1481605684@sss.pgh.pa.us
I don't know how to summarize that, so I'll just say "booleans in indexes are confusing"
Context
StackExchange Database Administrators Q#223586, answer score: 5
Revisions (0)
No revisions yet.