Thread

1. LIMIT clause extremely slow

   Melzer Kassensysteme <office@melzer-kassen.com> — 2023-11-15T16:46:21Z

   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
   
   Schlagturn 26, A-6135 Stans
   
   FN 463940s beim LG Innsbruck mit Sitz in Stans
   
   UID: ATU71726803
   
   Tel +43 (0) 5242 71361
   
    <http://www.melzer-kassen.com/> www.melzer-kassen.com
   
    <mailto:office@melzer-kassen.com> office@melzer-kassen.com
   
    
   
   

2. Re: LIMIT clause extremely slow

   Christophe Pettus <xof@thebuild.com> — 2023-11-15T17:48:37Z

   
   > 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 ... ?
   
   
   

3. Re: LIMIT clause extremely slow

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-11-15T18:08:51Z

   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