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 1pMuEO-0003dY-7h for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 17:15:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMuEL-0001iS-Ok for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 17:15:37 +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 1pMuEL-0001iJ-DX for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 17:15:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMuEI-00010X-Vr for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 17:15:36 +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 30VHFT8t1137030; Tue, 31 Jan 2023 12:15:30 -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: <8504ea0b-584f-773b-44c1-233f4ac8e630@gmx.net> References: <1079393.1675178901@sss.pgh.pa.us> <8504ea0b-584f-773b-44c1-233f4ac8e630@gmx.net> Comments: In-reply-to Dimitrios Apostolou message dated "Tue, 31 Jan 2023 17:32:10 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1137028.1675185329.1@sss.pgh.pa.us> Date: Tue, 31 Jan 2023 12:15:29 -0500 Message-ID: <1137029.1675185329@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dimitrios Apostolou writes: > On Tue, 31 Jan 2023, Tom Lane wrote: >> Do you get the same 10 rows when you repeat the command? > Yes. Just tested with both cold and hot caches. The first 10 rows are > exactly the same, either they return slowly or immediately. Hm. I don't recall exactly how synchronize_seqscans works --- it definitely changes the point in the table where a seqscan begins scanning, but I don't remember where/when that point gets updated (and I'm too lazy to go look). This result suggests that the first query advances the table's sync start point to someplace a bit before the first live tuple, not after all the returned tuples as I'd been thinking. It might be that the start point is always the beginning of a block, so that if you fetch only a few tuples that are all in the same block then the next attempt will re-fetch them. If you increase the LIMIT to say 1000, does the behavior (with synchronize_seqscans on) change? >> If turning synchronize_seqscans off changes the behavior, that'd >> be a good clue that this is the right theory. > Turning it off makes the query slow no matter how many times I re-run it. OK, well that's enough of a smoking gun to say that it is the sync start point that's the relevant state, and not the contents of your data caches at all. Anyway, the follow-on discussion makes it clear that you do indeed have a badly bloated table, and fixing that situation should fix this performance issue. regards, tom lane