HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
orderscancombinepostgresfieldswheresmartforandindex

Problem

I have the following table in 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


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

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"

Context

StackExchange Database Administrators Q#223586, answer score: 5

Revisions (0)

No revisions yet.