Select only last row from table for list of values

Question

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;

• jjanes: "Of course I can use executemany for this statement, but this is not very good for performance" Have you tried it? Please show the EXPLAIN (ANALYZE) for it.
• Erwin Brandstetter: executemany is not Postgres terminology. Are you thinking of MySQL? Or something like psycopg? Please also clarify: Postgres version, how many input values (min/max/avg), cardinality of t1, what to return exactly when no matching row is found for one / all input values. The best course of action depends on these details.

Answer 1

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:

• How to properly implement compound greatest-n filtering