I am currently trying to optimize a select query which takes 5-10 seconds on tables with 50k rows (and will grow 100x this soon). The purpose of this query is to find which tuples from a given set of (originalText, originalLang, newLang, description) tuples exist in the database (first three are from TranslationText and description is from Phrase).
The query has a massive tuple comparison WHERE clause with ~3-4k tuple comparisons. In addition this tuple spans multiple tables. I tried adding an index on (originalText, originalLang, newLang) in "TranslationText" but it actually slowed down the query.
QUERY (relevant parts)
SELECT * FROM "TranslationText" INNER JOIN "Phrase" ON "TranslationText"."phrase" = "Phrase"."id" [Several other JOINs] WHERE (("originalText" = $1 AND "originalLang" = $2 AND "newLang" = $3 AND "Phrase"."description" = $4) OR ( ~2k more similar tuple comparisons))
Here are the tables:
create table "TranslationText" ( id varchar(25) not null constraint "TranslationText_pkey" primary key, "originalLang" text not null, "newLang" text not null, "originalText" text not null, phrase varchar(25) constraint "TranslationText_phrase_fkey" references "Phrase" on delete set null, ... ); create table "Phrase" ( id varchar(25) not null constraint "Phrase_pkey" primary key, description text not null, ... );
Is there any way to optimize this tuple comparison query or should I not be running this in the first place? Is there a better way to check if a set of tuples exist in the database?
Using Postgres 11.4