Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNHdq-0004BZ-34 for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 18:15:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pNHdo-00007u-H2 for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 18:15:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNHdo-00007j-55 for pgsql-general@lists.postgresql.org; Wed, 01 Feb 2023 18:15:28 +0000 Received: from mout.gmx.net ([212.227.15.18]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pNHdk-0002JX-21 for pgsql-general@lists.postgresql.org; Wed, 01 Feb 2023 18:15:27 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1675275321; bh=fUk8M2buBsiU2iWSFYkwjDtPbYZ9GwmAaaNdHaar4kc=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:References; b=SVfhlzGES87VarmuxNGEb9I3l1ZbtljDTM8Mj8i+/ULkvoT+zLT8sPWe1rGfT6zkC KomGK2uh5FCevs4y8kBTudBhKKOwNAyjpNptHem3JVs3mVZKrb7XDqnaPC/oMsygQo cuey+nl8WTw73yXPUfdhcpmzY1GvA85OE1w/cWzR2eEnoKXMLOc0xFd6kHwt8N/b76 z/O/3wCG6RNkSAYitINA1yd5xG1B1YgJQfqDQNMhPdFJX1Vj4lfMy4qa0KpirZ6FIc vJGoev76/UCHOHB00NyODcpdQCj1PpX85PbNhuQX1Cdw2rkiLeAeUWpSrQtVkhGV2y +NEX+Y5AgtT+g== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.52] ([185.55.107.82]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MgvvT-1oijHo1u3B-00hOmE; Wed, 01 Feb 2023 19:15:21 +0100 Date: Wed, 1 Feb 2023 19:15:19 +0100 (CET) From: Dimitrios Apostolou To: "David G. Johnston" cc: Tom Lane , pgsql-general@lists.postgresql.org Subject: Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches In-Reply-To: Message-ID: <843f9f52-604b-246c-024e-c730b366203b@gmx.net> References: <1079393.1675178901@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="0-1922649098-1675275321=:17649" X-Provags-ID: V03:K1:Dt0JBdBePZbgoqm1ed2PryyML9nn+W0EQlKdRznaKCK1pc6a4rm DdDZhIWfX+AxDsuhdnvOy+g9V99nmftEQ3gA8yTuilEWAF2aKRWbYNTXawXUTrELT1z6n6c 4GYdJfDJWC1prKizXUNwyWV8owwaAMNfUmb1+rkk6u81rQbcuF2WWhLEEEE1U/X1AEA/0Jr igAEC09azie8jK/JtOOXQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:f9KDXHZnI0I=;0xg/vok/WzA5u/gP/fFVtfgdPOk aO6qJm2Nb16y0E/6+S/yLwhsfrEsBtSPq0dxurfpdYbhS6fwpnaASBrnSYUzku/Ov39tsxSTN 6sX1K7puHwWkFBl4lYTY6/6Cl/m+CVFYk0JJt6RgyMqCIOb4xQCOZ87s9pzP9DOO7bj/3M7JU rGFdWhAmHew8q+0bijCn8SAmZL9oRxpAzvg3iZ4pWnKLfZh73a/FM/7hgPZQ/7n7gTMG9GucB twZTTDUNWg2Jq8GuBLFjmAFmto6X5IwNp9EC/mZjwssnGlZlxi0nD6v5eZhAIzIUa4HKwStKZ bd5mklngi1zP+A60yO/r/IpfpHPIKaxqPr8fjRrUT34RirrnItlFQRavUiYkcMV/93E2wH+vn 8YM5SdaqKyXK5uTu3uU8qYuwlgfkp9N2uIm6JHFfnnoPQLc76rqQXjSdGVrE7MxGA6SfL4nof PKXWpL3OjIUYU4I6LMEC+rObWfJNhcZqUpzGgcc8M0eA36FJxIjBOD2F46MLzQ7wChL/GHZmc f3fc3ns1JDcpiGtaRC3uGxkvl5JfYLhCaWBRCzHop2783M9D6aJLmbGfClLouAApvE+/6ds+u NO/r96j9z7PUfSOhfl4zQfq65rrQ82BFzcNFE9t5OQgCYMmomFpG5qfubnGoxcNiHHOBcLrzX K+ZRBYkt9aDiAAUoflTfWHzZUZFEP0NfYXzoJMYaqo0qWwjasupa9L86U7FUGr3XGn9Kho1M6 MFzH/v3b4ngBvFVz74dz1W1sH97i6eTytyviEi2QZxeaLvvYgj5G8/NclH2HD7kZgdXFv4JeV KYKbyRgidtfAO4b7BVNn0n2BPTKlRUThN9eVZao66J3jL5Bk0KDh5Gge269ixdFZvcjlUTJUX iqivY7RU07wx1z5mI+cuJHjUJA499DTZoVAy2NB75H2EOLD5k4YbFxiwU List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. --0-1922649098-1675275321=:17649 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable On Tue, 31 Jan 2023, David G. Johnston wrote: > > It feels like there is room for improvement here using table statistics = and the visibility map to significantly reduce the number of pages retriev= ed that turn out to be all dead.=C2=A0 Yes, I too wish postgres was more clever for a simple unordered query. I should also mention that this table has two btree indices, one primary key index and one on another column. Would it be feasible to "descend" an index in order to immediately find some live data? > A limit without an order is not a query to optimize for of course... A good reason is that the web interface I'm using to browse my tables (and I guess many other too) do a simple limited+unordered SELECT when you click on a table. Timing out was what made me look deeper. I couldn't accept that no matter how slow my system is, it has trouble returning a few rows. It is also the first query somoebody will type on psql to interactively get a clue about the contents of a table. Regards, Dimitris --0-1922649098-1675275321=:17649--