Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3Jz2-0037Nr-WD for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Nov 2023 17:47:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r3Jz1-0016pF-IV for pgsql-bugs@arkaria.postgresql.org; Wed, 15 Nov 2023 17:47:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3J22-000ijH-Lv for pgsql-bugs@lists.postgresql.org; Wed, 15 Nov 2023 16:46:26 +0000 Received: from mx28lb.world4you.com ([81.19.149.138]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3J1z-005itZ-Na for pgsql-bugs@lists.postgresql.org; Wed, 15 Nov 2023 16:46:25 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=melzer-kassen.com; s=dkim11; h=Content-Type:MIME-Version:Message-ID:Date: Subject:To:From:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:In-Reply-To:References:List-Id:List-Help:List-Unsubscribe: List-Subscribe:List-Post:List-Owner:List-Archive; bh=KB8NQubOX7uf3FLYN+ra0JdZW/XK3wahg8oigGMA2Mk=; b=Ugym7ZcJkn4NG8lCg5cBeNhSIQ ZPpuRp+ncsc4mmveCRPp5aRJiITd1uVQ5XUp5ytZkmbLICLdUSWGH5hXZMmHWOuRrkcbqBoXairux xM2vwkE1MK5Rpld9nT+MnkZnIfpYDcPaV77mLc/sL1wjVbsXpeBTFV7eh8xYd5LRAlfc=; Received: from [80.110.12.97] (helo=FLO) by mx28lb.world4you.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96.2) (envelope-from ) id 1r3J1x-0001D2-0V for pgsql-bugs@lists.postgresql.org; Wed, 15 Nov 2023 17:46:21 +0100 From: "Melzer Kassensysteme" To: Subject: LIMIT clause extremely slow Date: Wed, 15 Nov 2023 17:46:21 +0100 Message-ID: <00f201da17e3$439404c0$cabc0e40$@melzer-kassen.com> MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_00F3_01DA17EB.A55ADDC0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdoX4mLtFYN3OL9vT/WFETOVvJ3PhQ== Content-Language: de-at X-AV-Do-Run: Yes List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00F3_01DA17EB.A55ADDC0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_00F4_01DA17EB.A55ADDC0" ------=_NextPart_001_00F4_01DA17EB.A55ADDC0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 www.melzer-kassen.com office@melzer-kassen.com ------=_NextPart_001_00F4_01DA17EB.A55ADDC0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

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

 

SELECT * = FROM table WHERE (index1 > 1 OR (index1 =3D 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

 

______________________= _____________________

3D"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

 

------=_NextPart_001_00F4_01DA17EB.A55ADDC0-- ------=_NextPart_000_00F3_01DA17EB.A55ADDC0 Content-Type: image/jpeg; name="image001.jpg" Content-Transfer-Encoding: base64 Content-ID: /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwkHBgoJCAkLCwoMDxkQDw4ODx4WFxIZJCAmJSMg IyIoLTkwKCo2KyIjMkQyNjs9QEBAJjBGS0U+Sjk/QD3/2wBDAQsLCw8NDx0QEB09KSMpPT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT3/wAARCABuASIDASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD2aiii gAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKA CiiigAooooAKKKKACiiigAooooAKKKKAMHxD4w0/w1d2kGoLNi5yRIigqgBAJPOe/bNbcUqTRJLE 6vG4DKynIIPQivNvi9Y3D/2fepEWtog8cjj+EkjGfrjrWV4A8cHRZU0zUpCdPkP7uQ/8sGP/ALKf 0rb2d4cyI5rOzPYaoazrNpoOmyXt9JtiTgAcs57KB3NS3+o2umafLe3cyx28a7mf+WPXNeGeK/FF z4n1Izy5jto8iCHPCD1P+0e/5VNOnzvyHKVj2vQNct/EWlJf2qSpEzMoWQANkHB6E1pVzHw7sbnT /B9rFdxNFI7PIEbqFY5GR24rp6mSSbSGtgoooqRhRRRQAUUUUAFFFFABRRRQAUUUUAFFFFABRRRQ AUUUUAFFFFABRRRQAUUUUAFFFFABRRRQAUUUUAYPjDxBP4a0db63tBdYlCOCxARSD8xIB74H41yN j8Xt95Gt/pqxWzHDyRyFmX3xjmvSZYkmiaOVFeNwVZWGQR6EV5D448APorPqGlI0mnnl4xy0H+K/ yranyPSRErrVHrH+iavp3/LK5s7lP95XU14r408Hy+GL7fEGk06Zv3Uh5KH+43v6HvS+DvGlz4Yu PKk3T6dI2ZIc8of7ye/t3r2E/wBm+J9EIBjurG6Tt3/wIP4g09aT8haTR4Nda1f3ml22n3Fy72lq SYoz2+p747Z6V2/w88DfaDFrOrRfuh81tAw+/wCjsPT0H4+lWtG+Ff2bxDJLqMqT6bCwaBP4pfQO OwHf1rsPEfiWx8L6f590d0jDEMCn5pD6D0Hqe1VOpf3YCjHqyfXdesvD2nNd30m1eiIPvSN6AV56 fjDdZO3SIcZ4BnOf/Qa43Wtbv/Emqfabxi8jHbFEgJCA9FUf5Jr0fwN8Pl03y9T1mMNefeigPIh9 z6t/L60uSMI3luPmcnodtptzLeaZbXE8JglliV3iPVCRkj8Kbql4dP0q7vFQO1vC8oUnAOATj9Kt 1meJf+RY1T/r0l/9BNYLVl9DI8HeObfxQJIJY1tb5Pm8rdkOvqp/mK6qvmu0up7K5iubWVop4mDI 69Qa9x8HeLoPE+nEttivoQPPiz/48P8AZP6dK1q0uXVbEwlfRnQu6xozyMFRQSzMcAD1NcfpHxBj 1vxcNKsrYNaFXK3JYgttGchcdDXKfEDxwdWkfStLk/0FDiaVT/riOw/2R+v0653wz/5Hi1/65S/+ g040rRcpCctbI9F8aeMZPCf2Py7Nbn7Tvzuk27duPY+tcv8A8Lhn/wCgPH/4EH/4mpPjF/zCPrL/ AOy1zvgnw5pfiD7b/at49t5Ozy9sipuznPUc9BVQhDk5mgbfNZHQQ/F6eWeOP+yIxvcLn7QeMnH9 2uy8X+Im8MaOt8lsLgmZY9hfb1B5zg+lc3B8OPDP2iIxatO8gcFVFxGckHOMYq38WP8AkUY/+vtP 5NUNQckkh6pO4nhP4gyeJda+wPp6W48ppN4l3dMcYwPWu3rxj4Wf8jj/ANu0n81r2epqxUZWQ4O6 OO8Y+O5PCupQWqWC3Alh8zcZduOSMdD6Vz//AAuGb/oDx/8AgQf/AImut8SeCLDxPexXV5PcxvFH 5YETKBjJPcH1rhfGvg7RfC2lpJDc3kl5O+2GN3XGB95jhegH6kVcPZuya1JlzIu/8Lhm/wCgPH/4 EH/4muw0TxLc6p4Vn1mewFuEWR4o/Mz5iqM5zjjJBFeO+GtCk8Ra5BYpkRk7pnH8EY6n+g9zXuGr QR2vhe9ggQJFHZyIijoAEIAoqxjGySCLb1OBHximIB/seP8A8CD/APE0f8Lhm/6A8f8A4EH/AOJr z7ToEur+0gmYpHLKiOwOMAkAmvTf+FbeGP8AoMTf+BEf+FaSjTjuhJyZ1HhDxG3ijR3vXthblZmi 2B93QA5zgetcdP8AF2aG4lj/ALIjOx2XP2g84OP7tdt4Y0Oz8P6UbbT53ngeQyB3YNknAPIGO1eC Xxxe3R9JX/8AQjUU4xlJ6Dk2kj0L/hcM3/QHj/8AAg//ABNdF4b+I+m69dJaTRvZXTnCLIwZXPoG 9fY4rNtvhRpFxZRS/a75XkjDfeXAJGf7teaatYSaPrF1ZGTdJbSlRIvGccg+3amoU56RFeS3Poyi szw5fvqnhzT7yX/WTQKz+7Y5/WtOudq2hqFFFFIAooooAKKKKACquo31rptjLdX8qRW6D52fp9Pf 6VHrGsWehadJe38uyJOAByWPZQO5NeIeKfFl54nvfMnPlWsZ/c24PCe59W9/yrSnTc/QmUrFLW7q yvNXuZ9MtTa2jtlIienqfbPXHavQfhXo+rWwkvpZGh02ZflgYf61uzgdh796qeBvh4bry9U1yIiH hobVhy/ozj09B3716LquqQaPZebJgt92OMdWPp9KuvWjGNugUqcpySS1ZfrxH4gaRq1jr0t3qUjX MM7HyJwPlC9kx/CR6d+vrXW2Hie8t9Ua5uHaWKU/vI88Af7I7Y/Wu0mgsdd0sxzIlzaXC8g9CP6H +VcuFxUZttHVisHOjbm6nj3w+1bSdJ1zfq0I3vhYbljlYT3yO2f73avbQwZQykEHkEd68O8Y+Crn wxcGWLdPp0hwkuOUP91/f0Per/gjx/JoZSw1Rml048I/VoP8V9u3b0rsqQ51zROKL5dGex1meJf+ RY1T/r0l/wDQTWhFLHPCksLq8bqGVlOQwPQis/xL/wAixqn/AF6S/wDoJrnW5o9j59toHuriGCIA ySuqICcZJOBUjfatNupoiZbadN0MqglWAPBU+xqXRP8AkO6d/wBfUX/oYr2LxZ4EtPE80Nysv2W6 UhXlVc+YnoR6jsa7ZzUXZmKjdHjEdhcSWE16kR+zQMqPJ0AZugHqa6P4Z/8AI8Wv/XKX/wBBrsfi Bplro/w8WysYhHBFPGAB1JzySe5PrXHfDP8A5Hi1/wCuUv8A6DU8/NBsdrNHQ/GL/mEfWX/2WuA0 /RNQ1jzP7Pspbrysb9gB256fyNd/8Yv+YR9Zf/Za5Lwv4uuvCv2n7JbwTfaNu7zSeNuemPrRTv7P QJW5tS94Z8I63aeJ9MuJ9JuI4orhWd2UYUevWu2+LH/Iox/9fafyaua/4W7quR/xL7L83/xro/iq d3g6I+t1Gf0aofNzxcilbldjkPhZ/wAjj/27SfzWvZ6+evD2vT+G9U+3WsUUsnlmPbJnGDj0+ldT /wALd1c8DT7Ik9AN/P60VacpSuhRkkj1p3WNGd2CqoySTwBXgfi/xAfEWvz3YY/Zk/d24PZB3/Hr XdeP/E11aeGLXTpwkOpX8Qa5SMnESfxD8Tx+deY2Vwtpew3DQpOInD+VJna2OgOO1OjC3vBN9D2P 4deG/wCw9DFzcJi9vQHfI5RP4V/qfc+1dBrv/Iv6j/16y/8AoJrzP/hb2q/9A+y/N/8AGus0fxDc eJvAmqXt1DFFIqTx7Y84wE9/rWc4SvzSKTWyPF4InneOKJC8khCqo6sTwBWv/wAIVr//AEBLr/vg f41l2Vy1nd29yihmhkWQKehIIOP0ruv+Fvap/wBA6y/76eumTkvhRmrdTu/A9lcaf4PsLa7haCaN W3RsMFfnY/1rwq//AOPy7/66yf8AoRr27wP4nufFOnXNzdQRQtFN5YEZJBG0Hv8AWvEb/wD4/Lv/ AK6yf+hGsqV+Z3Knsj1Dxb4z1Lw0NMtrBbcrNZq5aVCxB6ccivOILe/8Q6x5cQa4vbuQsxx3PVj6 AV6t4y8Nf274Qtp7dM3tnCskeBy67RuX+o9xXl/hzXZvD2tQX8OWVfllQfxoeo/qPcCnTty+7uKW +p71pVgml6Va2MZytvEsYPrgYzVuobS6hvrSK5tnEkMyB0YdwamrkZsFFFFABRRRQAUUUUAZmu6B ZeIrNLXUFdokkEgCOVOQCOo+prJsfhz4f0+9iuo7aSR4juUSyl1z2OD1rqaKpSaVkxWRV1HUYNMt GuLhsKOAo6sfQV5zqOoXGrXpmmyWb5UReQo7AVveOj/pFmM8bH4/EVb8MeHfsoW+vU/fkZjjP8A9 T7/yrzK/PXq+yWyPZw3ssNQ9vLVvYw77wzd2Olx3j/MessYHMY7fX3o8P68+kT+XKS1pIfnX+6f7 w/rXoRAYEEAg9Qa4PxL4fOmyG6tVJtHPIH/LM/4VNbDujapT6FYfFxxKdGt12/r8jtpI7fUbNkkW Oe2mTBBGVdTXLH4XeHCT+5uRnsJ24rU8HnPh+Lno7/zrcr0qVRuCktLnkVqahUcOzILK0isLKC0g BEUCCNATk4AwOaW8tY76zmtZwTFMjRuAcHBGDzU1VNVZk0m8ZGKsIXIIOCDg0N2VyYx5mkc9bfDT w/a3MM8UVwJInV1zOx5ByP5V1lc/plz/AGrfwfPMI7S3RirEoXkPcjqRgfTmr+tzulkIIX2T3TiF G/u56n8Bk1PtuaLmzV0OWah1/r9Bda0S01/TzZX6u0JYPhGKnI6ciszR/AmjaHqSX1jHOs6AqC8p YYIweK1NGuWudOTzT++iJil/3l4P59fxrIv8re3b6hNfW53f6NPHuMSLgYyBx1znNJ13GCa2YRw/ NNxe6Luv+FtO8S+R/aSyt5G7ZskK9cZ6fQVj/wDCrfDv/PK6/wC/7V1FyZ202Q2rq1wYj5bdi2OD WPpJiS+gUXN7DcMp82C63HzTjqCeMg+lU68otRQo0FOLl2M//hVvh3/nldf9/wBq39b0Cy8Qaetl fiQwq4cBH2nIyBz+NVvET7ZLBWa4EbzEOICwZhtPpz1q/pYiFkvki5Cbjxcbt/8A49zQqzlPl7Cd FRpqfc5r/hVnh3/nndf9/wA1PY/DjQNPvobuGGdpIXDoJJSy5HTIqSHUp4k1GG4c+XI8628meVZc /Jn9R+NbNk0r6JbshzMbdSCxzltvf8aIYlz0uOphvZ6syNY8B6PrmpSX18tw08gAO2YgAAYGBVL/ AIVZ4d/553X/AH/NXNMKx3VqJrm9t74nEyXG4pMccgfw9eRitHWHksjDqMbOY7c4njB4aM9Tj1HX 86UcRLl5uw5YZKah3/r8TC/4VZ4d/wCed1/3/NbWmeGrDSNGn0u1WUWs+/eGck/MMHn6VPo6Tm2e 5umbzblvM2E8Rr/CoH0/WscFo7lmv7m8tb3z/llbcYCu7hR/DgjjnvRLESsm+o44dOTSe39fcVR8 LPDoGPLuv+/5pf8AhVnh3/nndf8Af810WsWq3FhI5kmRokZ1MchXnHfHWm6HbLDp0MvmTO88aOxk kLc47Z6dav20+flM/ZQ5OYj0Hw7Y+G7aW305ZBHK/mNvcsc4A/pWJL8L/D00kjvHdbpGLHE56k5N WrRopdWufPOoNIt4yoYy/lgZGAccVoeIpbiGygazOJ/tCBRnAY56H2NZrEPlczR4b34w7mnFEsEK RJnaihRn0Fcrc/DPw9dXUs7Qzq0rlyqTEKCeTgdqstrMmo6ppwtw6Wwk2y54PmbSdn4d/wAKs+In Cy6ertcCJ5WDrAWDMNp9OetJYiycojeFfNGMt3r+f+Rc0bR7bQtOSxsvM8hCSokcsRk5PJq/VPSx GLJfJFwEyeLjdv6/7XNXK0UuZXMJR5XYKKKKYgooooAKKKKACiiigCGazt7iWOSaCOR4/uMyglfp U1FFKyG22FNkjSWNkkUMjDBVhkEU6imIigt4bWIR28aRxjkKgwKloooSsNtt3YVBeW/2qyntw23z Y2TdjOMjFT0UmrqwJtO6M86WVnsp4pdktunlOdvEiY5B/HkUXmkxahfRy3e2WCNCqwkcbieW/IYr QoqfZxtaxftpp3vqULDS0066na2IS3lCkQgcKw4JH14/KoLnS7yRrhIL/Zb3BJdHj3smRg7Tngfy rWoo9nG1hqtPm5upXazQ6f8AY1Z0QR+WGVsMBjAIPrVKHS7prq3kvrxZ1tiTGFi2knGMscnse1at FDhFiVWSTS6lDU7Ca8e2kt51hkt3Lgsm4HII6ZHrVi0S4jh23cyTSZPzIm0Y+mTU9FNQSfMJ1G4q L6GYdEjl0+7tJ23rcSvKGAwUJORj3FW4LXy9PjtWYnbEIyy8HpjI9KsUUlCK2Q5VZy0b8zIj0i7Z 7ZLu+E1vbOHQCPDsR03Nn/8AXV7ULU32n3FsH2GVCm7GcZqzRQqcUmu4OrJtS7DIk8uJEznaoGay ZdFupY3tXv8AdYu+4qyZkxnO3dnp+FbNFEoKWjCFWUHdEVxD59rLCDt8xCufTIxSWkH2WzggLbvK jVM4xnAxU1FVZXuRzO3KZUOm31tdTPBexLBLOZWjaHJ56jOfarV/ZG9SBQ+zypkl6ZztOcVboqVT ilYt1ZNqXVFK505Zri1ljKxiCYysAv3yQQf503U7Ca8ktpbedYZLdywLJuByCOmR61foocIu67gq sk0+xBaJcRwbbqZJpMn5lTaMfTJqeiiqSsrEN3dwooopiCiiigAooooAKKKKACiiigAooooAKKKK ACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooA /9k= ------=_NextPart_000_00F3_01DA17EB.A55ADDC0--