pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT clause extremely slow
3+ messages / 3 participants
[nested] [flat]
* LIMIT clause extremely slow
@ 2023-11-15 16:46 Melzer Kassensysteme <office@melzer-kassen.com>
2023-11-15 17:48 ` Re: LIMIT clause extremely slow Christophe Pettus <xof@thebuild.com>
2023-11-15 18:08 ` Re: LIMIT clause extremely slow Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 3+ messages in thread
From: Melzer Kassensysteme @ 2023-11-15 16:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
Attachments:
image001.jpg (image/jpeg, 5.5K) download
view image
* Re: LIMIT clause extremely slow
2023-11-15 16:46 LIMIT clause extremely slow Melzer Kassensysteme <office@melzer-kassen.com>
@ 2023-11-15 17:48 ` Christophe Pettus <xof@thebuild.com>
1 sibling, 0 replies; 3+ messages in thread
From: Christophe Pettus @ 2023-11-15 17:48 UTC (permalink / raw)
To: Melzer Kassensysteme <office@melzer-kassen.com>; +Cc: pgsql-bugs@lists.postgresql.org
> 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 ... ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LIMIT clause extremely slow
2023-11-15 16:46 LIMIT clause extremely slow Melzer Kassensysteme <office@melzer-kassen.com>
@ 2023-11-15 18:08 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2023-11-15 18:08 UTC (permalink / raw)
To: Melzer Kassensysteme <office@melzer-kassen.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-11-15 18:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-15 16:46 LIMIT clause extremely slow Melzer Kassensysteme <office@melzer-kassen.com>
2023-11-15 17:48 ` Christophe Pettus <xof@thebuild.com>
2023-11-15 18:08 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox