Sorted indexes and pagination: `DESC` vs `DESC NULLS LAST` #1040

New issue

Open

opened 2025-12-31 22:05:48 +00:00 by Oneric · 0 comments

Oneric commented

2025-12-31 22:05:48 +00:00

Owner

Our SQL pagination helper lib/pleroma/pagination always sorts by DESC NULLS LAST or ASC NULLS LAST. However it typically acts on primary keys or other non-nullable columns. If now an index was created with just DESC or ASC postgres won’t use the index at all or use it, but still load all entries, resort them and only then limit.

In particular this affects "context" queries. In lib/pleroma/web/activity_pub.ex itself there are multiple functions querying all or just the mo st recent post of a context by ordering by the activity id using a plain DESC. However, when we query the N most recent posts for conversation timelines (up until now) another "DESC NULLS LAST" order statement got applied.

Given we, to my knowledge, never use this on nullable fields, we might as well drop the NULLS LAST. However, we’ll need to recheck all related indexes and make sure they are also changed if necessary.

Our SQL pagination helper `lib/pleroma/pagination` always sorts by `DESC NULLS LAST` or `ASC NULLS LAST`. However it typically acts on primary keys or other non-nullable columns. If now an index was created with just `DESC` or `ASC` postgres won’t use the index at all or use it, but still load _all_ entries, resort them and only then limit. In particular this affects `"context"` queries. In `lib/pleroma/web/activity_pub.ex` itself there are multiple functions querying all or just _the_ mo st recent post of a context by ordering by the activity `id` using a plain `DESC`. However, when we query the `N` most recent posts for conversation timelines (up until now) another "DESC NULLS LAST" order statement got applied. Given we, to my knowledge, never use this on nullable fields, we might as well drop the `NULLS LAST`. However, we’ll need to recheck all related indexes and make sure they are also changed if necessary.

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

AkkomaGang/akkoma#1040

No description provided.

Rows
Columns

Sorted indexes and pagination: DESC vs DESC NULLS LAST #1040

Sorted indexes and pagination: `DESC` vs `DESC NULLS LAST` #1040