Postgres Pro
Downloads
Home   >   mailing lists

Thread: LIMIT clause extremely slow

LIMIT clause extremely slow

From
"Melzer Kassensysteme"
Date:

Hi,

 

I have an issue using a limit clause, f.ex.

 

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 under same conditions (and I have to repeat this command very often, so time is adding up).

I have read in some forums that you can fasten it up by saying

ORDER BY index1 + 0, index2 + 0

The result is better, but still very slow.

 

Using version 16.1

 

Thanks for your help and best regards, Florian Melzer

 

___________________________________________

MELZER_GmbH_WBK_bo_pos_4c

Melzer GmbH

Schlagturn 26, A-6135 Stans

FN 463940s beim LG Innsbruck mit Sitz in Stans

UID: ATU71726803

Tel +43 (0) 5242 71361

www.melzer-kassen.com

office@melzer-kassen.com

 

Attachment

Re: LIMIT clause extremely slow

From
Christophe Pettus
Date:

> On Nov 15, 2023, at 08:46, Melzer Kassensysteme <office@melzer-kassen.com> wrote:
>
> SELECT * FROM table WHERE (index1 > 1 OR (index1 = 1 AND index2 > 5)) ORDER BY index1, index2 LIMIT 1

Can you show the output of the query running under EXPLAIN ANALYZE ... ?

Re: LIMIT clause extremely slow

From
Laurenz Albe
Date:
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