Horton

Re: LIMIT clause extremely slow

Laurenz Albe <laurenz.albe@cybertec.at>

View thread
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Melzer Kassensysteme <office@melzer-kassen.com>, pgsql-bugs@lists.postgresql.org
Date: 2023-11-15T18:08:51Z
Lists: pgsql-bugs 
On Wed, 2023-11-15 at 17:46 +0100, Melzer Kassensysteme wrote:
> SELECT * FROM table WHERE (index1 > 1 OR (index1 = 1 AND index2 > 5)) ORDER BY index1, index2 LIMIT 1
>  
> index1 and index2 are index fields of datatype integer.
>  
> This takes some 100 times longer than in Mysql or other databases

This is not a bug; at worst, it is a performance problem.

For good performance, rewrite the query to

  SELECT * FROM "table"
  WHERE (index1, index2) > (1, 5)
  ORDER BY index1, index2
  LIMIT 1;

and make sure you have an index on (index1, index2).

Yours,
Laurenz Albe