Arvados

https://www.postgresql.org/docs/current/indexes-ordering.html

By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).

18943-created-at-index @ f988449749c047bbf100e94dd2bb9285b08fa3c1

developer-run-tests: #3009

• Migrate indexes for collections, links, logs, groups, and users to two-column index (created_at, uuid)
• Fix keyset_list_all() in python SDK so that "ascending=False" parameter results in query (created_at desc, uuid desc) which will use the index, instead of (created_at desc, uuid asc) which won't use the index.

The modified_at indices are (modified_at DESC, uuid) so it looks like keyset_list_all will now perform better with the default order_key="created_at" but worse with order_key="modified_at".

I'm thinking it would be better (fewer special cases) if we make both sets of indexes work in the same direction, so we can use the same uuid-direction rule in keyset_list_all for both created_at and modified_at, and still get decent performance.

18943-created-at-index 06c2fb95a229a00a3a39ac84db60062c94fe8f32

developer-run-tests: #3012

Add
index_#{table}_on_created_at_and_uuid
and
index_#{table}_on_modified_at_and_uuid

Drop extraneous indexes (e.g. indexes on created_at or modified_at only)

Adjust the default query order to (modified_at desc, uuid desc) to match the adjusted modified_at_and_uuid index ordering.

This will have minor effect on ordering for default queries. If two records have the same modified_at, the tiebreaker will now order by descending uuid, instead of ascending uuid.

The down-migration probably shouldn't re-add the (created_at, uuid) index after deleting it.

Rest LGTM, thanks.

18943-created-at-index @ 58d86f7855278212a5c7b4dad2fea03f653740af

• Fix tests that explicitly checked the sort order
• Tweaked test fixtures which had the same created_at time (mostly because of copy&paste) to have different created_at times, because order by uuid changed

developer-run-tests: #3015

Once remaining tests are fixed, LGTM (looks like there's still a workbench1 test that's sensitive to the fixture timestamps)

18943-created-at-index @ 39cfbff065282869d8dadab2474dfaae4ba0b86a

developer-run-tests: #3019

LGTM