Re: Best way to check if a table is empty

On Sunday, March 23, 2025, Marcelo Fernandes <marcefern7(at)gmail(dot)com> wrote:

> Hi folks,
>
> I came up with three strategies to verify whether a table is empty.

3 is strictly terrible worse to answer “is live row count > 0”.

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’t
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.