entrouvert/wcs
entrouvert
/
wcs
14
1
Fork 0
Code Issues Pull Requests 40 Releases Activity

sql: change _fid idx on evolution table to contain id too 

This will improve several queries on tables where not that many different formdata,
where sorting cost is (wrongly) considered worse than the cost offetching the whole table.
This commit is contained in:
Pierre Ducroquet 2022-07-28 11:35:08 +02:00
parent 1233847add
commit 4932d67265
1 changed files with 23 additions and 7 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

30
wcs/sql.py
View File

@ -979,21 +979,37 @@ def do_formdef_indexes(formdef, created, conn, cur, concurrently=False):
existing_indexes = set()
if not created:
cur.execute(
'''SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename IN (%s, %s)''',
'''SELECT pci.relname, array_length(pi.indkey, 1)
FROM pg_index pi
JOIN pg_class pci on pci.oid = pi.indexrelid
JOIN pg_class pct on pct.oid = pi.indrelid
WHERE pci.relnamespace = 'public'::regnamespace
AND pct.relname in (%s, %s)''',
(table_name, evolutions_table_name),
)
existing_indexes = {x[0] for x in cur.fetchall()}
existing_indexes = {x[0]: x[1] for x in cur.fetchall()}
create_index = 'CREATE INDEX'
if concurrently:
create_index = 'CREATE INDEX CONCURRENTLY'
if evolutions_table_name + '_fid' not in existing_indexes:
fid_idx = evolutions_table_name + '_fid'
if fid_idx in existing_indexes and existing_indexes[fid_idx] != 2:
# The index was previously (< #67757) on formdata_id only, so
# search for it with only one key and recreate it if needed.
if concurrently:
cur.execute('DROP INDEX CONCURRENTLY %s' % fid_idx)
else:
cur.execute('DROP INDEX %s' % fid_idx)
cur.execute(
'''%s %s_fid ON %s (formdata_id)''' % (create_index, evolutions_table_name, evolutions_table_name)
'''%s %s_fid ON %s (formdata_id, id)'''
% (create_index, evolutions_table_name, evolutions_table_name)
)
elif fid_idx not in existing_indexes:
cur.execute(
'''%s %s_fid ON %s (formdata_id, id)'''
% (create_index, evolutions_table_name, evolutions_table_name)
)
for attr in ('receipt_time', 'anonymised', 'user_id', 'status'):