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 1pMsN0-0007vI-Eg for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 15:16:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMsMy-0003U4-W2 for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 15:16:24 +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 1pMsMy-0003Tv-K6 for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 15:16:24 +0000 Received: from mout.gmx.net ([212.227.17.20]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMsMw-0008Dx-Jk for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 15:16:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1675178180; bh=/i8USmoLL1eyCZ4pkuq1Pkp9JQrmw02LBnE4daaBASU=; h=X-UI-Sender-Class:Date:From:To:Subject:In-Reply-To:References; b=QTGQ98pFmpTwGIiA4t1b5R9BarxFynd3ZulbuBMjlnqO9utMa/Pfzs5b/FXPbXrhm lFUYf0C94GQywZkYdZzEu6USAHkGfySVnT59pnwySxkc9Ga70KQMhJXe3rSo0/rXX+ emM/5iu5VBsNf+QG8uigkDGCp4MjA0UnDI+oF3h/gE+K7XgT9OSHexmA4SYM5VqA8j ZyP3sS6E/3us9bD4YYNP3hyiNfyEG6XCrKNxBUO8W7Z8GGi0mcnc1z2aQ5ttaJbahT o6X/DNzY2Yee1ojq7dppux+EH8CbShXdSfhS1dVKfY3mZv8Lk2ustfqanNIBTBmn/y Rmx7+gbgvQjpA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.52] ([185.55.107.82]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M59GG-1pNypU0zno-0016tZ for ; Tue, 31 Jan 2023 16:16:20 +0100 Date: Tue, 31 Jan 2023 16:16:16 +0100 (CET) From: Dimitrios Apostolou To: 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: <505fa76e-e68f-90de-0506-3ebdab5e5a61@gmx.net> References: MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:79lZv6BNG2Dkfz6gxCb9TYuDXv5K6TkVTIR4l+pB0aUNxpqmg5b 6EbXuEkMEBmoqsMx7qZ2XgTzXvqPHL7SNo6xOKu9Snz3WAf+DAcpwzdoMOCdzU7KRSsLFgh nCcqSsax0MUI1SZMvEzm8HfutQsFRkqE3sgisnbGnn8TTYZbizhnIHuO8uSUKZ5XMfAS6qJ tjg79JIYHfONtRm+Wjx+Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:G2aUoAeHGsk=;WBDItW889/4klZfZooj/g5WKZXc tKP4QK+b6eRkjP1CxU6S3y+Bv/nfChyqCnlgQa8dzqWKNEqdasuvg+LceF+/NbpgT1/R+fMaL IlHQZ6HnaMgfMAhyunzpSTATtYyWbvwurUQzRczmUS4aRoKBupFRz8RMb5oF9OtALIonoOnPy kYV4MGPHKDfGNTP166w0MrJD97ZKiPS0mY5IZUBlhEljOhfdRHyUu7IyzoNFStpqKpyGzlOhn PtkRwSIqmTFQZTjOheMEmAXju0kGf7rUW1VmSh8ajPGpPjUjKysoIGsgGvKZNE2QgLZyWXDR+ YtRFPMO+2NNZh/4KQ96qKHHEioDwqw1fAmcsMPoDq5+Yy6DPQ+iey3mENn3yHcTKwC4YkhgkH XkhvUZvuDXq9GPYv8UFo0LeIJYOLxyBf25NlhX0fS6WVvLsRmx9YAe9bIsBwg6Wn5skQtY5zz JU/o71RGzk/T+hVwUbeHSM4mWuetidYuOX8Q2u0Ei2GzkDDNR80eH2KwEKDYFAOXnOBYLPh9X NI+ZTR9o9UjWhi58kMsrUbXQiZxKkpM9X0ivJ6kdPEQMaXPIP1t3OuwxSpHP3CIiEE5AsK9r+ MiZ/x+AqYKC0V0n8GmSicz+0bi8G/Hh8zlVep0hrq8g0G/tE4Odut9q/r+X6HV3ANFSiUFbto y2tidsWaEpTMhDjmTubCAgkmKI6o/eNdIbK140GALyd0htlu/NLI4MAQYJ+5g/H4Wb0q9LJlA NF3q1yKAojoZSnYrNOgzGYbvDWveolBMtTJMJVOrFPxz41nXmva6PYdboch3gOIM0gF7J+0oY o/bEVxu2NKbeP7PaapTr3nHkwhWrfb3p/4mH35BrTu0a1bhO/zz+QLYChFH+xLlkFl2Y/wk/Z JZrvIJ5HIZulXCp09oa4ZE2PMjlRGL3+1uvxywvfMOFbDzMyugidF5g2PNy7yIbeC7V7mIWZ1 Xqe7XQ== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I managed to double-paste different subset of my settings. FWIW all of them are true for all the experiments. Sorry for the confusion. On Tue, 31 Jan 2023, Dimitrios Apostolou wrote: > Other postgres settings: > > shared_buffers =3D 1024MB > effective_io_concurrency =3D 0 > wal_compression =3D zstd > wal_recycle =3D on > min_wal_size =3D 1GB > max_wal_size =3D 10GB > checkpoint_timeout =3D 15min [...] > Relevant settings: > > synchronous_commit =3D off > wal_recycle =3D on > min_wal_size =3D 1GB > max_wal_size =3D 10GB > track_io_timing =3D on > track_wal_io_timing =3D on > wal_compression =3D zstd