ITADN

Index missing for messageattempt

#2183Openaskkaz 创建于 2026-02-13
A
askkazcommented
## Feature Request ### Motivation In [this PR](https://github.com/svix/svix-webhooks/pull/2059), the relation to `messagedestination` was dropped and the listByEndpoint query now relies on `messageattempt`. These queries have the pattern of: ``` SELECT messageattempt.id, messageattempt.created_at, messageattempt.msg_id, messageattempt.msg_dest_id, messageattempt.endp_id, messageattempt.url, messageattempt.status, messageattempt.response_status_code, messageattempt.response, messageattempt.ended_at, messageattempt.trigger_type, messageattempt.response_duration_ms, messageattempt.next_attempt, messageattempt.attempt_number FROM messageattempt WHERE messageattempt.endp_id = ? AND messageattempt.id > ? AND messageattempt.id < ? ORDER BY messageattempt.id DESC LIMIT ?; ``` There seems to be no matching index and we are seeing very slow performance on this endpoint. The `messagedestination` table had [the following indexes](https://github.com/svix/svix-webhooks/blob/ca54b59fbf8d7acd6089abe219a2ce2b3ac123ff/server/svix-server/migrations/20220123205937_initial.up.sql#L111): ``` CREATE INDEX ix_messagedestination_per_endp_no_status ON messagedestination USING btree (endp_id, id DESC); CREATE INDEX ix_messagedestination_per_endp_with_status ON messagedestination USING btree (endp_id, status, id DESC); ``` When things switched to `messageattempt`, [this index](https://github.com/svix/svix-webhooks/blob/ca54b59fbf8d7acd6089abe219a2ce2b3ac123ff/server/svix-server/migrations/20250909214223_messageattempt_endp_idx.up.sql#L1) was created: ``` CREATE INDEX IF NOT EXISTS messageattempt_per_endp_unified ON messageattempt (endp_id, status, id DESC) INCLUDE (response_status_code); ``` Since the query for listByEndpoint can be run without specifying status, the planner cannot make use of this index. I think it was an oversight that an equivalent index to `ix_messagedestination_per_endp_no_status` was not created. ### Proposal Add this index: ``` CREATE INDEX ix_messageattempt_per_endp_no_status ON messageattempt USING btree (endp_id, id DESC); ``` ### Alternatives Could be I'm missing something!
5 条评论