PostgreSQL Performance – Select Only Last Row for List of Values

greatest-n-per-groupperformancepostgresqlpostgresql-performance

As example we have a table:

CREATE TABLE t1 (
   a BYTEA NOT NULL,
   r_pointer BIGINT,
   s_pointer BIGINT,
   data BYTEA,
   PRIMARY KEY(a, r_pointer)
) PARTITION BY HASH (a);

In case we I want get last element:

SELECT * FROM t1 WHERE a ='\xaaa' order by r_pointer desc limit 1;

Of course, I can use executemany for this statement, but this is not very good for performance.

How can I get only the first row found for each element in a given array like {\xaaa,\xbbbb}. Similar to the following, but this returns all rows:

SELECT * FROM t1
WHERE  a = ANY ('{\xaaa,\xbbbb}'::bytea[]) 
ORDER  BY r_pointer DESC;

Best Answer

For big tables, modern PostgreSQL (your example suggests Postgres 11, since PKs are not supported for partitioned tables before that), and many input values, this should perform excellently:

SELECT t1.*
FROM   unnest('{\xaaa,\xbbbb}'::bytea[]) a
CROSS  JOIN LATERAL (
   SELECT *
   FROM   t1
   WHERE  t1.a = a.a
   ORDER  BY t1.r_pointer DESC
   LIMIT  1
   ) t1;

Your PK on (a, r_pointer) provides just the index needed for this.

Related:

Related Question