public inbox for [email protected]  

help / color / mirror / Atom feed

From: Marcelo Fernandes <[email protected]>
To: [email protected]
Subject: Best way to check if a table is empty
Date: Mon, 24 Mar 2025 16:42:19 +1300
Message-ID: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com> (raw)

Hi folks,

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

I wanted to sound the community to check whether my assumptions are correct for
each of these strategies, and to also discuss which strategy is best.

## Strategy 1 [possibly best?]

SELECT EXISTS (SELECT 1 FROM foo LIMIT 1);

Pros:
  1. Works with any table.
  2. Relatively fast (if the table is well organised).

Cons:
  1. Sequential Scan
  2. If the table is bloated, it reads more buffers.

## Strategy 2

SELECT min(id) FROM foo;

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.

Cons:
  1. Sequential Scan if the table does not have a PK index.
  2. Reads a few more buffers than Strategy 1 when the table is well organised.
  3. Performs worse if the index is bloated.

## Strategy 3 [worst]

SELECT count(*) FROM foo;

Pros:
  1. Uses a widespread and intuitive operation (count)

Cons:
  1. Very slow on large tables as it performs a Sequential Scan.


How does all of that sound? Are there further strategies I should consider?
Anything I have missed in the Strategies above?

Regards,
Marcelo.

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]
  Subject: Re: Best way to check if a table is empty
  In-Reply-To: <CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@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