From: | Marcelo Fernandes <marcefern7(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Best way to check if a table is empty |
Date: | 2025-03-24 03:42:19 |
Message-ID: | CAM2F1VMbOEubpXk44B5KaWKX0OSVrA8-9xqidhJMNtDprhTSTg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2025-03-24 04:03:37 | Re: Best way to check if a table is empty |
Previous Message | Christophe Pettus | 2025-03-24 01:34:24 | Re: Determine server version from psql script |