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;

"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. — jjanes, Apr 26 '19 at 17:29
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. — Erwin Brandstetter, Apr 27 '19 at 23:57

Erwin Brandstetter · Accepted Answer · 2019-04-28T00:25:24.963

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.

How to properly implement compound greatest-n filtering

Select only last row from table for list of values

1 Answers1