Improve performance of selected SQL queries
Background
With SQLAlchemy v2 upgrade some SQL queries are slowed down. In addition, upon inspection, some queries can benefit from indexes and other minor changes.
Implementation
- Add
force_unnest
kw argument to filters.in_ (defaultFalse
) to circumvent fallback if length of iterable is less than 100 (always use SQLsunnest
). Use ongenotype_query
ingenotypetable.py
- Create indexes: genotype (allele_id), genotypesampledata (sample_id), genotypesampledata (multiallelic), genepanel_transcript (transcript_id)
-
queries.annotation_transcripts_genepanel
shifts to a horrible plan if number of allele ids passed is >60 (from ~40ms to 2000ms). One way to circumvent this is to create a batch of selects, each with len(allele_ids) < 60, andunion_all
at the end. This query is used quite extensively throughout.
Edited by Øyvind Evju