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 “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.
