We introduced our first query hint into our code today, and based on this wiki page I figured I report it as a bug. Our table looks something like:
CREATE TABLE IF NOT EXISTS threads ( account_id VARCHAR(64), group_id BIGINT, thread_id VARCHAR(64), "timestamp" BIGINT NOT NULL, has_unread BOOLEAN NULL, CONSTRAINT pk_threads PRIMARY KEY (account_id, group_id, thread_id)
);
CREATE UNIQUE INDEX threads_by_owner_group_time_id ON threads (account_id, group_id, "timestamp", thread_id);
CREATE UNIQUE INDEX threads_by_owner_group_unread_time_id ON threads ( account_id, group_id, has_unread, "timestamp", thread_id );
We have a query that is a lookup - essentially this:
SELECT * FROM threads WHERE account_id = ? AND group_id = ? AND thread_id = ?;
Usually everything is fine and this does a query on the primary key index - but sometimes postgres decides to pick the threads_by_owner_group_unread_time_id index.
Picking the index is an issue because it ends up scanning essentially the whole index range for a given account_id and group_id. Due to the way upgrading predicate locks works - this almost always ends up locking the entire table if that group has enough threads. This causes a ton of contention in our database as this query happens for every incoming message (we have to book keep the has_unread column properly) and the predicate lock on the whole table interferes with every other active account's processing (we processing all events for a single account sequentially).
I think this index is being picked over the primary key because it's queried frequently and is likely to be cached or something. Anyways at any other isolation level this would be fine, but at serializability it ends up hurting throughput because we now retry a ton transactions unnecessarily.
I think there are two possible improvements here:
Pick the primary key in this case (which is what our hint does).
Happy to answer specific questions about this.