On 2/17/22 19:55, Tyler Rockwood wrote:
> We introduced our first query hint into our code today, and based on
> this wiki page
> <https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion>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.
>
Unlikely - we don't use usage stats for query planning. You're probably
getting different query plans for parameter values with different row
count estimates. And if the estimates are wrong (which may happen if the
columns are correlated in some way), the estimates may be off, and the
optimizer can pick the wrong plan.
But it's really hard to give more insight because you didn't show the
query plans, and you also didn't mention the Postgres version.
See https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
