Re: LIMIT clause extremely slow - Mailing list 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