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 1pMsYf-0008Jr-K4 for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 15:28:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMsYe-0008O4-DA for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 15:28:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMsYe-0008Mv-27 for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 15:28:28 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMsYb-0007fA-V5 for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 15:28:27 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 30VFSLge1079394; Tue, 31 Jan 2023 10:28:21 -0500 From: Tom Lane To: Dimitrios Apostolou cc: 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: References: Comments: In-reply-to Dimitrios Apostolou message dated "Tue, 31 Jan 2023 16:07:31 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1079392.1675178901.1@sss.pgh.pa.us> Date: Tue, 31 Jan 2023 10:28:21 -0500 Message-ID: <1079393.1675178901@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dimitrios Apostolou writes: > The question is why this simple query is taking so long to complete. Do you get the same 10 rows when you repeat the command? On the basis of the limited info you provided, I'm going to guess that (1) there are huge subranges of the table containing no live rows, so that a seqscan might have to pass over many blocks before it finds some to return; (2) once you do reach an area having live rows, the next SELECT picks up scanning in that same area due to the effects of "synchronize_seqscans", so you get immediate answers until you reach the next desert of dead tuples. If turning synchronize_seqscans off changes the behavior, that'd be a good clue that this is the right theory. As for a real fix, it might be time for a VACUUM FULL or CLUSTER on that table. regards, tom lane