Index missing for messageattempt

[askkaz]

Feature Request

Motivation

In this PR, 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:

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 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!

[tasn]

I think you are correct, will wait on the team to respond.

[svix-james]

Thanks for catching this @askkaz! New migration has been merged.

[svix-james]

@askkaz Apologies, but we've had to revert this for now due to issues encountered in internal testing. Note that if you've already run/tested the migration, you should be able to manually delete the relevant row in sqlx_migrations after reverting the code.

[svix-james]

Reopening.

[askkaz]

@askkaz Apologies, but we've had to revert this for now due to issues encountered in internal testing. Note that if you've already run/tested the migration, you should be able to manually delete the relevant row in sqlx_migrations after reverting the code.

all good!