Index missing for messageattempt
## 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 条评论