Select rows based on long list of two columns

Question

Say I have the following table

CREATE TABLE IF NOT EXISTS "PROPS" (
   "O_TYPE_ID"  UUID NOT NULL,
   "O_ID"       UUID NOT NULL,
   "R_TYPE_ID"  UUID NOT NULL,
   "NAME"       VARCHAR NOT NULL,
   "VALUE"      VARCHAR,

   CONSTRAINT PK_PROPS  PRIMARY KEY ("O_ID", "R_TYPE_ID", "NAME")
    );

Now I have a list of lists which is the list I want to use to query Postgres. Meaning I have a list of

List((O_ID.type, NAME.type))

I want to list all the records which have the combination of these two types, a single list I could use the IN operator, how can I query for list os list values in the above case? A naive implementation would be to do a where with two equal clause and the and operator, but in my case if the list is too huge that would mean too much IO, how do you handle the scenario to handle the list of lists in a very optimized way without too much IO. Using 9.4+ Postgres.

Erwin Brandstetter · Accepted Answer · 2020-05-19T02:17:39.813

One of many possible ways: pass two arrays, one array of O_ID(let's name it o_id_arr), one array of NAME (let's name it name_arr); then unnest in parallel and join:

SELECT p.*
FROM   (o_id_arr, name_arr) AS t("O_ID", "NAME")
JOIN   "PROPS" p USING ("O_ID", "NAME");

The feature of unnesting multiple arrays in parallel was introduced with Postgres 9.4, so should work for you:

Unnest multiple arrays in parallel

Or, especially for big tables and very long lists, create a temporary table, insert your data into it, optionally ANALYZE it, then join to it:

CREATE TEMP TABLE tmp ("O_ID" UUID, "NAME" VARCHAR);

INSERT INTO tmp VALUES (..., ...), (..., ...);

ANALYZE tmp;

SELECT p.*
FROM   tmp
JOIN   "PROPS" p USING ("O_ID", "NAME")

Normally you should have a multicolumn index on "PROPS" ("O_ID", "NAME") for performance. But seeing that "O_ID" is type uuid, the odd PK index on ("O_ID", "R_TYPE_ID", "NAME") might do the job. Why "odd"? Two UUID and a varchar column seem too bloated for a good PK ...

Are PostgreSQL column names case-sensitive?

Your unquoted constraint name PK_PROPS is saved as pk_props ...

Select rows based on long list of two columns

1 Answers1