There is a table in which the search always takes place in two columns - origin and destination . The values ​​of the columns are a three-digit string - MOW , LED , etc. The table stores about 50,000 records and requests for it are executed for 2 seconds. To speed up the queries came up with the following algorithm:

  1. Create a new column called key . Write the values ​​from the origin and destination fields into it, separating them with a dash. It turns out the record type MOW-LED , which will always be unique.
  2. Create a unique index ( CREATE UNIQUE INDEX ) for the table by the value of the newly created key column

How can this be done with a single SQL query?

  • one
    What for? why not to make two current fields - indexes? Shl. for information postgrespro.ru/docs/postgrespro/9.5/indexes-multicolumn - Vladimir Klykov
  • @ Vladimir Klykov unless the search will not be even faster if you look for one column, and not two? - JamesJGoodwin 4:38
  • No, there is no, you are doing a composite index, and the search in it will go like one field (updated comment above, link to description) - Vladimir Klykov
  • I will add another point - the composite index can be unique ( UNIQUE ), which will give another plus to an exact search .... - Vladimir Klykov
  • The values ​​of the columns are a three-digit string - MOW, LED, etc. 18 thousand options for one field, 300 million for two. Those. two fields are packaged perfectly into one type of INT. Indexing - nowhere easier. Unpacking is elementary. - Akina

0