Postgres ordering is not consistent

Question

I'm having an issue where postgres ordered results are not ordered consistently. I'm ordering by multiple fields: ORDER BY categories.position ASC, photos.display_priority

I'm noticing this because as you browse around the site the results are paginated. I found a case where I go from page 1 to page 2, and at the top of page 2 I see a photo that was near the bottom of page 1.

Here is my page 1 query:

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 25 OFFSET 0;

And my page 2 query:

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 25 OFFSET 25;

When I try getting both pages at once (offset 0, limit 50) and inspect the threshold between the two sets there is no duplicate, no surprise.

SELECT "photos".*
FROM "photos"
    INNER JOIN "categories" ON "categories"."id" = "photos"."category_id"
WHERE "photos"."category_id" IN (221, 633, 377, 216, 634)
    AND (photos.caption IS NOT NULL
        AND photos.category_id IS NOT NULL
        AND photos.rights IS NOT NULL
        AND photos.deleted IS NULL)
ORDER BY categories.position ASC, photos.display_priority DESC
LIMIT 50 OFFSET 0;

Is there something wrong with my query? Is there an order of operations with limit and order by which I'm not understanding?

this is still reproducible, we got to the same issue...

vitali_y
– vitali_y

2021-10-08 07:53:13 +00:00
Commented Oct 8, 2021 at 7:53 — vitali_y
– vitali_y, Commented Oct 8, 2021 at 7:53

cdhowie · Accepted Answer · 2013-08-28 22:17:07Z

5

It sounds like categories.position and photos.display_priority are not unique for all of the result rows. The database server does not specify the order for rows when the values used to order them are all equal; it is free to return them in any order, even if the table data has not changed between queries.

To get consistent ordering you will have to add a third sorting key that is guaranteed to be unique for all rows, such as the identity value for that particular row.

answered Aug 28, 2013 at 22:17

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Keith Schacht Over a year ago

You're right. That was the cause. Odd. I get that the order of those results would be random, but I thought it would still be consistent. I never imagined that from one query to the next where I'm just changing the offset that the order of those would change. Anyway, thanks for helping me out!

cdhowie Over a year ago

@KeithSchacht No problem. The reason that the rows were reordered may have been because the underlying table changed. There are many factors that may impact the order rows are returned in. Even if one row is added at the very end of the data set (after ordering) this can impact how the other rows get sorted. Other operations that don't change the data itself but simply rearrange it (like VACUUM FULL or CLUSTER) will often change how rows with the same order are arranged relative to each other. Ultimately, you just can't assume consistent ordering of same-order rows. :)

Keith Schacht Over a year ago

I get that in principle the ordering could change. The bizarre thing in this case is that it's reproducible. For this particular query, every time I do the first page LIMIT it's one way, and every time I do the second page LIMIT it's ordered a different way. This is with no changes to the DB in between. I'm sure it's just some underlying SQL engine optimization but this is a nice lesson for me to keep in mind as I'm building apps.

cdhowie Over a year ago

@KeithSchacht Yes, it could very well be caused by the LIMIT clause differing. The planner may be generating a slightly different plan for each query, for example.

Collectives™ on Stack Overflow

Postgres ordering is not consistent

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related