Project

General

Profile

Actions

Bug #18943

closed

[db] add missing created_at,uuid indexes

Added by Ward Vandewege 3 months ago. Updated 3 months ago.

Status:
Resolved
Priority:
Normal
Assigned To:
Category:
-
Target version:
Start date:
04/01/2022
Due date:
% Done:

100%

Estimated time:
(Total: 0.00 h)
Story points:
-
Release relationship:
Auto

Description

The default behavior of keyset_list_all() in the Python SDK is to use `order created_at asc, uuid asc` to page through a table comprehensively, so we need to have this index.


Subtasks 1 (0 open1 closed)

Task #18949: Review 18943-created-at-indexResolvedPeter Amstutz04/01/2022

Actions

Related issues

Related to Arvados - Task #18904: review 18903-fix-activity-scriptResolvedWard Vandewege03/24/2022

Actions
Related to Arvados - Bug #18903: [user activity script] does not always initialize user object and other fixesResolvedWard Vandewege03/24/2022

Actions
Actions #1

Updated by Ward Vandewege 3 months ago

  • Description updated (diff)
Actions #2

Updated by Ward Vandewege 3 months ago

  • Related to Task #18904: review 18903-fix-activity-script added
Actions #3

Updated by Ward Vandewege 3 months ago

  • Description updated (diff)
  • Subject changed from [db] add missing created_at,uuid index on the logs table to [db] add missing created_at,uuid indexes
Actions #4

Updated by Ward Vandewege 3 months ago

  • Related to Bug #18903: [user activity script] does not always initialize user object and other fixes added
Actions #5

Updated by Ward Vandewege 3 months ago

  • Release set to 46
Actions #6

Updated by Peter Amstutz 3 months ago

  • Assigned To set to Lucas Di Pentima
Actions #7

Updated by Peter Amstutz 3 months ago

  • Assigned To changed from Lucas Di Pentima to Peter Amstutz
  • Status changed from New to In Progress
Actions #8

Updated by Peter Amstutz 3 months ago

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).

Actions #9

Updated by Peter Amstutz 3 months ago

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.
Actions #10

Updated by Tom Clegg 3 months ago

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.

Actions #11

Updated by Peter Amstutz 3 months ago

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.

Actions #12

Updated by Tom Clegg 3 months ago

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

Rest LGTM, thanks.

Actions #13

Updated by Peter Amstutz 3 months ago

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

Actions #14

Updated by Tom Clegg 3 months ago

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

Actions #16

Updated by Tom Clegg 3 months ago

LGTM

Actions #17

Updated by Peter Amstutz 3 months ago

  • Status changed from In Progress to Resolved
Actions

Also available in: Atom PDF