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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1twZDB-00Fcyc-QU
	for pgsql-general@arkaria.postgresql.org; Mon, 24 Mar 2025 04:14:53 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1twZDA-00BzlF-Db
	for pgsql-general@arkaria.postgresql.org; Mon, 24 Mar 2025 04:14:52 +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 <xof@thebuild.com>)
	id 1twZDA-00Bzl6-2s
	for pgsql-general@lists.postgresql.org; Mon, 24 Mar 2025 04:14:52 +0000
Received: from smtp113.iad3b.emailsrvr.com ([146.20.161.113])
	by magus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <xof@thebuild.com>)
	id 1twZD7-000nCr-0U
	for pgsql-general@lists.postgresql.org;
	Mon, 24 Mar 2025 04:14:51 +0000
X-Auth-ID: xof@thebuild.com
Received: by smtp15.relay.iad3b.emailsrvr.com (Authenticated sender: xof-AT-thebuild.com) with ESMTPSA id D49E0C0147;
	Mon, 24 Mar 2025 00:14:46 -0400 (EDT)
Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.1\))
Subject: Re: Best way to check if a table is empty
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com>
Date: Sun, 23 Mar 2025 21:14:15 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <691954B8-AB7C-4A7D-904A-79547BDC364F@thebuild.com>
References: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com>
To: Marcelo Fernandes <marcefern7@gmail.com>
X-Mailer: Apple Mail (2.3776.700.51.11.1)
X-Classification-ID: 3462c46e-f441-49af-86af-dd1a2b745711-1-1
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/691954B8-AB7C-4A7D-904A-79547BDC364F%40thebuild.com>
Precedence: bulk



> On Mar 23, 2025, at 20:42, Marcelo Fernandes <marcefern7@gmail.com> =
wrote:
> Cons:
>  1. Sequential Scan
>  2. If the table is bloated, it reads more buffers.

These concerns probably sound worse than they really are.  Assuming the =
table is being vacuumed reliably, and is receiving inserts, those =
inserts will tend to be at the start of the table, and so you'll hit a =
live tuple pretty fast.  There are pathological cases where it will take =
a long time (someone just delete a DELETE FROM t; from a huge table and =
the entire thing is dead tuples but no free space), but that's =
relatively unlikely to happen in a real-world system.

> Pros:
>  1. Does an index-only scan on a field that presumably has a PK index.
>  2. Works well even if the table is bloated.

#1 is not necessarily true.  You're never guaranteed an index-only scan; =
PostgreSQL may decide that the visibility map information indicates that =
a regular index scan (which means heap fetches to check visibility) will =
still happen.
=20
#2 is not necessarily true either; it's pretty much false in the same =
situation that a sequential scan for the first tuple will be slow.  =
Indexes get bloated, too, and if there are a large number of dead =
unreclaimed tuples, there are also going to be a lot of dead index =
entries for it to walk over.

(And to be pedantic, #1 returns TRUE / FALSE while #2 returns <int> / =
NULL, so they aren't exactly equivalent.  If you are able to handle =
<int> / NULL, you don't need the EXISTS clause.)

> SELECT count(*) FROM foo;

There's no situation in which this will be faster than #1 or #2.=