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)

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

  • 1
    Do you have a composite index on ("originalText", "originalLang", "newLang")? Do you have an index on (description)? – ypercubeᵀᴹ Sep 24 at 8:43
  • 1
    Also: if you have more joins, do add the whole query in the question. And the EXPLAIN plan you get. – ypercubeᵀᴹ Sep 24 at 8:44
  • 3
    It is hard to figure out your question. If the you can reproduce the problem with your simplified query, then you don't need to mention all the things you didn't include (at least not in ways which break the syntax). If you do need those things you left out, then you need to not leave them out. – jjanes Sep 24 at 14:02

Very often joining to a VALUES clause improves performance in these cases:

FROM "TranslationText"
  JOIN "Phrase" ON "TranslationText"."phrase" = "Phrase"."id"
  [.. your other joins ...]
  JOIN (
        ($1, $2, $3, $4), 
        ($5, $6, $7, $8), 
  ) as v(org_text, org_lang, new_lang, description)
      on v.org_text = "originalText" 
     AND v.org_lang = "originalLang" 
     AND v.new_lang = "newLang" 
     AND v.description = "Phrase"."description"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.