Sqlite has a feature to allow custom sort order: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_collation We might find that advantagous to use it when ordering rows from the shard_range table.

bench_get_namespaces.py

#!/usr/bin/env python
import argparse
import sys
import timeit

from swift.common.utils import Namespace
from swift.container.backend import ContainerBroker

parser = argparse.ArgumentParser()
parser.add_argument('path', help='path to the .db')
parser.add_argument('--states', default='listing',
                    choices=['listing', 'updating'],
                    help='which states')
parser.add_argument('-n', default=10, type=int,
                    help='how many iterations')


def collate_max_upper(a, b):
    if a == '':
        return 1
    elif b == '':
        return -1
    else:
        # apparently cmp is gone in py3!?
        return (a > b) - (a < b)


def do_collate_max_upper(broker, states):
    sql = '''
    SELECT name, lower, upper FROM shard_range
    WHERE name != ? AND state IN (%s)
    ORDER BY upper COLLATE MaxUpper, state, lower, name
    ''' % ','.join('?' for _ in states)
    with broker.get() as conn:
        conn.create_collation('MaxUpper', collate_max_upper)
        cur = conn.execute(sql, [broker.path] + states)
        cur.row_factory = lambda cur, r: Namespace(*r)
        return [r for r in cur]


def test_get_namespaces(broker, states):
    expected = broker.get_namespaces(states=states)
    actual = do_collate_max_upper(broker, states)
    assert actual == expected


def main():
    args = parser.parse_args()
    broker = ContainerBroker(args.path)
    states = list(broker.resolve_shard_range_states([args.states]))
    # sanity!
    test_get_namespaces(broker, states)

    # do bench
    _globals = {
        'broker': broker,
        'states': states,
        'do_collate_max_upper': do_collate_max_upper,
    }
    print('get_namespaces')
    print(timeit.timeit('broker.get_namespaces(states=states)',
                        number=args.n, globals=_globals))
    print('do_collate_max_upper')
    print(timeit.timeit('do_collate_max_upper(broker, states)',
                        number=args.n, globals=_globals))


if __name__ == "__main__":
    sys.exit(main())

example.out

vagrant@saio:~$ python /vagrant/.scratch/namespaces.py /srv/node1/sdb1/containers/54/c48/dbb45b15fb6a3d12bd0e2c733a79cc48/dbb45b15fb6a3d12bd0e2c733a79cc48_1696282510.37315.db
0 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-0', lower='', upper='obj0099')
1 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-1', lower='obj0099', upper='obj0199')
2 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-2', lower='obj0199', upper='obj0299')
3 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-3', lower='obj0299', upper='obj0399')
4 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-4', lower='obj0399', upper='obj0499')
5 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-5', lower='obj0499', upper='obj0599')
6 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-6', lower='obj0599', upper='obj0699')
7 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-7', lower='obj0699', upper='obj0799')
8 Namespace(name='.shards_AUTH_test/lots-of-files-9933d06bdb1de26a4adfa0a7692feae9-1696282510.36864-8', lower='obj0799', upper='obj0899')
9 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-0', lower='obj0899', upper='obj08990099')
10 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-1', lower='obj08990099', upper='obj08990199')
11 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-2', lower='obj08990199', upper='obj08990299')
12 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-3', lower='obj08990299', upper='obj08990399')
13 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-4', lower='obj08990399', upper='obj08990499')
14 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-5', lower='obj08990499', upper='obj08990599')
15 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-6', lower='obj08990599', upper='obj08990699')
16 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-7', lower='obj08990699', upper='obj08990799')
17 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-8', lower='obj08990799', upper='obj08990899')
18 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-9', lower='obj08990899', upper='obj08990999')
19 Namespace(name='.shards_AUTH_test/lots-of-files-0adf6bc49056a6dd14202666a874c69f-1696283212.96429-10', lower='obj08990999', upper='')

results.out

vagrant@saio:~$ python /vagrant/.scratch/bench_get_namespaces.py  /vagrant/.scratch/dbs/nvetl-data-prod.db 
get_namespaces
1.275238914999818
do_collate_max_upper
2.165850151999848
vagrant@saio:~$ python /vagrant/.scratch/bench_get_namespaces.py  /vagrant/.scratch/dbs/nvetl-data-prod.db --states updating -n 25
get_namespaces
3.146130034999942
do_collate_max_upper
5.4766581079998105