Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

Manually set n_distinct for event_search.room_id to avoid bad Postgres query plans #14402

Closed
erikjohnston opened this issue Nov 10, 2022 · 1 comment · Fixed by #14409
Closed

Manually set n_distinct for event_search.room_id to avoid bad Postgres query plans #14402

erikjohnston opened this issue Nov 10, 2022 · 1 comment · Fixed by #14409
Assignees
@squahtx
Labels
A-Message-Search Searching messages O-Frequent Affects or can be seen by most users regularly or impacts most users' first experience S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.

Comments

@erikjohnston
Copy link
Member

On matrix.org we discovered that every now and again event_search queries would take forever due to using the wrong query plan. This was due to Postgres underestimating the number of room IDs that existed.

To fix this we should add a schema update with:

ALTER TABLE event_search ALTER COLUMN room_id SET (n_distinct = -0.01);

like we do for state_groups_state.state_group already.
@squahtx squahtx added S-Major Major functionality / product severely impaired, no satisfactory workaround. A-Message-Search Searching messages O-Frequent Affects or can be seen by most users regularly or impacts most users' first experience T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues. labels Nov 10, 2022
@squahtx squahtx self-assigned this Nov 10, 2022
@DMRobertson DMRobertson mentioned this issue Nov 10, 2022
Closed
@squahtx
Copy link
Contributor

squahtx commented Nov 10, 2022

like we do for state_groups_state.state_group already.

See #10359 for that fix.

squahtx pushed a commit that referenced this issue Nov 10, 2022
Fix PostgreSQL sometimes using table scans for event_search
4dba1f3 
PostgreSQL may underestimate the number of distinct `room_id`s in
`event_search`, which can cause it to use table scans for queries for
multiple rooms.

Fix this by setting `n_distinct` on the column.

Resolves #14402.

Signed-off-by: Sean Quah <[email protected]>
@squahtx squahtx mentioned this issue Nov 10, 2022
Merged
squahtx added a commit that referenced this issue Nov 10, 2022
@squahtx
Fix PostgreSQL sometimes using table scans for event_search (#14409)
b2c2b03 
PostgreSQL may underestimate the number of distinct `room_id`s in
`event_search`, which can cause it to use table scans for queries for
multiple rooms.

Fix this by setting `n_distinct` on the column.

Resolves #14402.

Signed-off-by: Sean Quah <[email protected]>
@squahtx squahtx mentioned this issue Nov 17, 2022
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@squahtx squahtx

Labels
A-Message-Search Searching messages O-Frequent Affects or can be seen by most users regularly or impacts most users' first experience S-Major Major functionality / product severely impaired, no satisfactory workaround. T-Defect Bugs, crashes, hangs, security vulnerabilities, or other reported issues.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix PostgreSQL sometimes using table scans for event_search matrix-org/synapse
2 participants
@squahtx @erikjohnston