public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Marcelo Fernandes <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Best way to check if a table is empty
Date: Sun, 23 Mar 2025 21:03:37 -0700
Message-ID: <CAKFQuwY_N9QeJZJ3sgYC7Ui0nohZVX5NCzELanXKmpiVgxZbag@mail.gmail.com> (raw)
In-Reply-To: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com>
References: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com>

On Sunday, March 23, 2025, Marcelo Fernandes <[email protected]> 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.

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Best way to check if a table is empty
  In-Reply-To: <CAKFQuwY_N9QeJZJ3sgYC7Ui0nohZVX5NCzELanXKmpiVgxZbag@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox