0

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 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 at 23:57
0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.