public inbox for [email protected]  

help / color / mirror / Atom feed

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



> On Mar 23, 2025, at 20:42, Marcelo Fernandes <[email protected]> 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.
 
#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.

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: <[email protected]>

* 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