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 1tk99L-000bZ4-6Y for pgsql-general@arkaria.postgresql.org; Mon, 17 Feb 2025 21:59:35 +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 1tk99J-003sXQ-Ti for pgsql-general@arkaria.postgresql.org; Mon, 17 Feb 2025 21:59:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tk99J-003sXB-IC for pgsql-general@lists.postgresql.org; Mon, 17 Feb 2025 21:59:33 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tk99H-001QoJ-1T for pgsql-general@postgresql.org; Mon, 17 Feb 2025 21:59:33 +0000 Received: by mail-oi1-x22e.google.com with SMTP id 5614622812f47-3f3ff03c89dso422378b6e.2 for ; Mon, 17 Feb 2025 13:59:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739829570; x=1740434370; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=u3BR0MEMYJCp1ILtxLO8ITUGU9NLpGiaKDQfT82iJ1s=; b=OjI4bKb7KcOMjkzlCLWObLaWJ764NrLIKqMzWivM+w94DsUTqCcUpKM2AV3aTSEpEh EDeWNIX+JZV/xEJ/i1uAqC1Vq06lrbOQlQFg5VJ+jAhrM95ku6E4b7x17VCurdvuE3y3 Cz9t+Sj3k2OV8LF0ZzeCNQIjuwRVk60dVTILQL+C7l4pn4CWvu7Da4ETn8U//jC05nXu RlFE5CwlaT0BEF3t0167AgjMR91w2ZgxZScvz9pxzuMbMMcZe9aUfBWu3o0FGS1tNJ0l 6rT75IyBjDMFnI1rWONHpSf3rYw1zCel0vD3FdJ3fT66e44iJS0xYMkp6e0PnsYq+Gnn wCYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739829570; x=1740434370; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=u3BR0MEMYJCp1ILtxLO8ITUGU9NLpGiaKDQfT82iJ1s=; b=XfAMN0OTHrEU2nQzkQ3otSVlx8+KGRXptvKhpj8U8YtTN721re6K+kosCFZNqCT/ld zVfbQFN0wRh+wcgLi6dLmXzR0p1wqu1tw6I53fwTsvyvRiWfh2OX5n4MxCCp5a8cZoPj 2NwvGnrI2Vn/Qa8Y91Ilql+CKJAu5eViHsvzYcEA1GVPUReNOADoDeB0ogPMyPFi8MIS 9T2aVlvzCuxbN+RKGkHGEmEFMjAuoisdz1ggYrvBeagncTtElxFRqXErQZbNI6Re5gDY cQo9Tl7Z6Z0TILVz8OxcGmSYEQizEK3h82SqxToDsgs3eqvykrmEHpjwfaknaDBTXPb6 EsCw== X-Gm-Message-State: AOJu0Yx8F85fHjhOkL5imdZ5Eto0+fVYrGu6PHjb4MerKAI0KtTP5Lko tGejFfING5PL0ErYb0ncvoPCqfTqxoFZlhdm5m6j61jgeDq8sQ6kD7MyFleS7sTkTB+8saqDU0s e/MuPms1Uu9Gnkv8xroNAk5ya2o4= X-Gm-Gg: ASbGncvNJNYILAcVTNTniEx+NJCmS+Pq/3h06SedwUb3GuKlEybb/+cf2X3IF7X60S3 nYzcvisrlZzewZXZxSBVQ0yG1bRTjpf2reRuMG6eusG0RXFVIWgvpgfk0jbSQwVfiGoyuii0= X-Google-Smtp-Source: AGHT+IF6dy6EwqPdjdW3l8AXUf/0wFD5cLHo0GgzW7F8NJSpVZ7sMsZbqym46rElzS1uhysUyFk76z0SELLk5rgf6NQ= X-Received: by 2002:a05:6808:18a3:b0:3f4:600:7f58 with SMTP id 5614622812f47-3f40600878bmr1382819b6e.35.1739829569686; Mon, 17 Feb 2025 13:59:29 -0800 (PST) MIME-Version: 1.0 References: <931647.1739828197@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Mon, 17 Feb 2025 14:58:53 -0700 X-Gm-Features: AWEUYZnoYmTH4pJTWd0d91fXRDsS18W7qjdQz_Z_q1wG8MXZYgUxaf7qzpVLR0s Message-ID: Subject: Re: Loading the latest N rows into the cache seems way too fast. To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000007892b5062e5da3ea" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007892b5062e5da3ea Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 17, 2025 at 2:41=E2=80=AFPM Ron Johnson wrote: > On Mon, Feb 17, 2025 at 4:36=E2=80=AFPM Tom Lane wrot= e: > >> Ron Johnson writes: >> > The bigint "id" column in "mytbl" is populated from a sequence, and so >> is >> > monotonically increasing: the newest records will have the biggest id >> > values. >> > The table also has a bytea column that averages about 100KB. >> >> > Loading 200K rows is more than 200MB. I expected this "prewarm" >> statement >> > to take much longer than 1/2 second. Am I still in the dark ages of >> > computer speed, or is this statement not doing what I hope it's doing? >> >> It's not pulling in the TOAST storage where the bytea column lives. >> (pg_prewarm wouldn't have either, without special pushups.) >> > > Puzzling, since I ran "PERFORM *". What if I explicitly mentioned the > bytea column's name? > > It's more about the system optimizing away data retrieval because you've indicated you don't care about the contents due to using PERFORM. All it needs is a pointer to represent the future data, not the data itself. And PERFORM will never resolve that pointer by itself - so as Tom said your query would need to force pointer resolution by computing on the data. David J. --0000000000007892b5062e5da3ea Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 17, 2025 at 2:41=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wr= ote:
On Mon, Feb 17, 2025 at 4:36=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> The bigint "id" column in "mytbl" is populated fro= m a sequence, and so is
> monotonically increasing: the newest records will have the biggest id<= br> > values.
> The table also has a bytea column that averages about 100KB.

> Loading 200K rows is more than 200MB.=C2=A0 I expected this "prew= arm" statement
> to take much longer than 1/2 second.=C2=A0 Am I still in the dark ages= of
> computer speed, or is this statement not doing what I hope it's do= ing?

It's not pulling in the TOAST storage where the bytea column lives.
(pg_prewarm wouldn't have either, without special pushups.)

Puzzling, since I ran "PERFORM *".=C2=A0= What if I explicitly=C2=A0mentioned the bytea column's=C2=A0name?


It's more ab= out the system optimizing=C2=A0away data retrieval because you've indic= ated you don't care about the contents due to using PERFORM.=C2=A0 All = it needs is a pointer to represent the future data, not the data itself.=C2= =A0 And PERFORM will never resolve that pointer by itself - so as Tom said = your query would need to force pointer resolution by computing on the data.=

David J.

--0000000000007892b5062e5da3ea--