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 1twZ2P-00FbSZ-UP for pgsql-general@arkaria.postgresql.org; Mon, 24 Mar 2025 04:03:45 +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 1twZ2N-00Bocq-AW for pgsql-general@arkaria.postgresql.org; Mon, 24 Mar 2025 04:03:43 +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 1twZ2M-00Boci-VB for pgsql-general@lists.postgresql.org; Mon, 24 Mar 2025 04:03:42 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1twZ2K-000n8T-2V for pgsql-general@lists.postgresql.org; Mon, 24 Mar 2025 04:03:42 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-6003d977ab7so2317590eaf.2 for ; Sun, 23 Mar 2025 21:03:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742789019; x=1743393819; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=g7eEKrpr0LdJkdeF/gcCF3gJ6C9relRtU929TqEypBc=; b=MLKYrkFT8R4Z+o8iEbBPBQO8SCfCV8xPKTr4Q3GUv+DiuYrngAphUmHEHCuPFjikbX fD3VzM8fbbjTQaxAK/Nm/wJKF0Ot1Di1vaxzSL71vRBedPa2au0cfvrSdgArFr9B8Fl8 BKijQ2mJP9KQyhHIcIVftx+T2mMaK5PB0wD+fCN6Zx+2sG8gmm/FM9VxbM2DztpW0ZHK K/PEkQT+2YWHDyt/HWLXDb7t2TfJ4Ods+6IFe6cecDD6nlrFuB+3b0zwWuMVbejIHhtZ OUZ4JnOGKOtHEhaLwa0nUEwrI+4wrCNE8hzwJfzpwTgTdet2G3W6c74XRqa1WAXV7AW9 z2PA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742789019; x=1743393819; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=g7eEKrpr0LdJkdeF/gcCF3gJ6C9relRtU929TqEypBc=; b=XzjQtZxkuRQ9DEqhVTelPM+QZYH2Tx9bGQB6tzVb2ooYEcdb2lmSoX2NXfAyHL7r8j yqAPNUYv4Snm0ZEr1GWaZeheSrzdD38J5LCHRG320Pdj7JcHbXvu11wdYltK2bgdb8/5 QAvfsqxL+6F+bs1S+WbtVdNa3kA/nBd+G8Kh1JkeuouIMkuQvnFaOZdxyms8ppKyNOvr jPPTUmCeZdOCDLG2wxNZrCQB86Qp90FVYu/ITgpDq9v0JWgWgKYdU7ENcv4wp3zWFh6O gaZLjT8Qs8zSysy1mLrVBcgt2v3bQuVbxM9NAI0j9jvCQqc2rT0E8ohuums40ZV5+jcM NTHw== X-Gm-Message-State: AOJu0Yx4xu3bEROL0jg8g5u9LM+O2yQRlxQaLTYiSc/0F/iGsjOl9pkf bPxdOzNNDvXp/d8LsU0EdHj6MVOKgIwd/NzJ0rV8GWk825nm3w9gIY3W85hBEYKVeOQ+0qPHRNn cMLPnoZDz7tJxgdpIRTJzo5tjon4= X-Gm-Gg: ASbGncvJSxBV3CzhEAvIptas4ZWmIjTgR2Jtxdvn01mN7e1LSXdorxCZBVsPtu+9e4V nATSTR8KKvIGnmnyuP2hBHWOnz7/gRrx9befEEoWFMmMi3701oFnQM4oloP8Ggoo0xir/d/LpjC IlwpYFygsM/pu90750wRbQUvwU X-Google-Smtp-Source: AGHT+IGBNQi3oOFO7udVpSZUPhfUVb6vfH3ozk2/wHuPVesYg3dq4OYyrNTije9kspuQU3JG7AO0vBf+MTbQvGAn7I4= X-Received: by 2002:a05:6870:204e:b0:29e:32e7:5f17 with SMTP id 586e51a60fabf-2c7804c903amr7649826fac.28.1742789018587; Sun, 23 Mar 2025 21:03:38 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a8a:349:0:b0:589:13f9:e937 with HTTP; Sun, 23 Mar 2025 21:03:37 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Sun, 23 Mar 2025 21:03:37 -0700 X-Gm-Features: AQ5f1JoTIrz8PzEjAqGtC3468DpQ3aHYEHx1hkF9JVoNEb79-qfdms3lXmoGt9w Message-ID: Subject: Re: Best way to check if a table is empty To: Marcelo Fernandes Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000005f1d0906310eb014" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005f1d0906310eb014 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sunday, March 23, 2025, Marcelo Fernandes wrote: > Hi folks, > > I came up with three strategies to verify whether a table is empty. 3 is strictly terrible worse to answer =E2=80=9Cis live row count > 0=E2=80= =9D. Using an index likely serves no/negative benefit since it contains no tuple liveness information and you now are doing more buffers and lookups (IOW, your claims about pro/con for min(id) require assumptions you haven=E2=80= =99t stipulated apply here). Maybe IOS helps though I do wonder whether a sequential scan skips over known all-dead pages making that relative benefit go away. So, absent data and an idea of why, just start looking at heap pages until you find a live tuple, then stop. Exists already optimizes for early stop, the limit is pointless. Checking table statistics first is probably a net-positive for many use cases. But maybe rethink your data and processing models if doing this check on large bloated tables is what your existing choices have led to. David J. --0000000000005f1d0906310eb014 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sunday, March 23, 2025, Marcelo Fernandes <marcefern7@gmail.com> wrote:
Hi folks,

I came up with three strategies to verify whether a table is empty.

3 is strictly terrible worse to answer =E2=80=9Cis= live row count > 0=E2=80=9D.

Using an index li= kely serves no/negative benefit since it contains no tuple liveness informa= tion and you now are doing more buffers and lookups (IOW, your claims about= pro/con for min(id) require assumptions you haven=E2=80=99t stipulated app= ly here).=C2=A0 Maybe IOS helps though I do wonder whether a sequential sca= n skips over known all-dead pages making that relative benefit go away.

So, absent data and an idea of why, just start lookin= g at heap pages until you find a live tuple, then stop.=C2=A0 Exists alread= y optimizes for early stop, the limit is pointless.

Checking table statistics first is probably a net-positive for many use c= ases.

But maybe rethink your data and processing m= odels if doing this check on large bloated tables is what your existing cho= ices have led to.

David J.

--0000000000005f1d0906310eb014--