In PostgreSQL, considering `table.id` column is primary key, what's the best ? - **A:** `select count(*) from table;` - **B:** `select count(id) from table;` - **C:** `select count(distinct id) from table;`

Sonar Pro

Vote: A and B

Since `table.id` is the primary key, it has no NULLs or duplicates, so all three queries return the same row count. However, **A** (`count(*)`) and **B** (`count(id)`) are optimal because they are fastest—they skip unnecessary distinct processing or column-specific checks[1][3]. **C** (`count(distinct id)`) is slower, as PostgreSQL must perform a hash aggregate or sort to eliminate non-existent duplicates, taking ~742ms vs. faster plain counts on million-row tables[1][3]. Options like G include the suboptimal C, while H wrongly dismisses all. D correctly identifies the two best.

GPT-5.4

Vote: A

`count(*)` is the canonical way to count rows in PostgreSQL. Since `id` is a primary key, every row has exactly one non-NULL `id`, so `count(*)` and `count(id)` return the same result. However, `count(*)` expresses the intent directly: count rows. It avoids tying the query to a particular column and is generally the preferred, idiomatic form. `count(distinct id)` is unnecessary. A primary key is already unique and non-NULL, so adding `distinct` only introduces extra work with no benefit. So the best single choice is `A`. `B` is equivalent in result, but not better than `count(*)`.

Claude Opus 4.6

Vote: A

In PostgreSQL, `SELECT COUNT(*) FROM table;` is the best option for counting all rows. Here's why: **`COUNT(*)` vs `COUNT(id)`:** While both return the same result when `id` is a primary key (and thus NOT NULL), `COUNT(*)` is semantically clearer — it means 'count all rows.' `COUNT(id)` technically checks each value for NULL before counting, adding unnecessary overhead even though the primary key constraint guarantees no NULLs exist. The query planner may optimize this away in some cases, but `COUNT(*)` is still the canonical and most direct approach. **`COUNT(DISTINCT id)` is strictly worse:** Since `id` is a primary key, all values are already unique. Adding `DISTINCT` forces PostgreSQL to perform an unnecessary deduplication step (sorting or hashing), which adds overhead for zero benefit. This makes option C clearly suboptimal. **Why not D (A and B)?:** While A and B produce the same results, B is not equally 'best.' `COUNT(*)` is the idiomatic and recommended way to count rows in PostgreSQL. It avoids the unnecessary NULL check semantics and is universally recognized as the standard row-counting pattern. The PostgreSQL documentation and community best practices consistently recommend `COUNT(*)` for this purpose. Therefore, option A — `SELECT COUNT(*) FROM table;` — is the single best choice.

Gemini 3.1 Pro

Vote: A

In PostgreSQL, `count(*)` is the optimal and recommended way to count the number of rows in a table. Even though `id` is the primary key (and therefore NOT NULL), `count(id)` forces PostgreSQL to check each row to ensure the `id` value is not null before incrementing the count, adding unnecessary overhead. Similarly, `count(distinct id)` is the least optimal because it introduces unnecessary uniqueness checks, which require hashing or sorting, despite the primary key already guaranteeing uniqueness. Thus, `count(*)` (Option A) avoids these extraneous checks and evaluates faster.

Grok 4.1 Fast

Vote: A

All three queries return the same result: the total number of rows, since `id` is a primary key (non-null and unique). However, `A: count(*)` is the most efficient. PostgreSQL optimizes `count(*)` using the visibility map to skip all-visible pages during sequential scans, avoiding unnecessary column fetches. `B: count(id)` requires fetching the `id` column for every tuple (even with index-only scans on the primary key, it has slight overhead compared to `count(*)`). `C: count(distinct id)` incurs significant extra cost for deduplication (hashing/sorting), unnecessary given uniqueness. Thus, `A` is strictly the best for performance and simplicity.

Kimi K2.5

Vote: A

**Option A (`count(*)`) is the standard and most efficient way to count rows.** PostgreSQL specifically optimizes `count(*)` to simply count rows in the relation without fetching any column data. It allows the query planner to choose the smallest available index (including non-PK indexes if they exist), minimizing I/O. **Option B (`count(id)`) is functionally equivalent but semantically inferior.** While the result is identical because primary keys are NOT NULL, explicitly naming the column suggests a need to exclude NULLs. It also potentially constrains the planner to use the specific column's index rather than a smaller alternative, and it makes the query fragile to schema changes. **Option C (`count(distinct id)`) is suboptimal.** A primary key guarantees uniqueness by definition, so the `DISTINCT` clause forces an unnecessary sort or hash aggregation, significantly increasing execution time and resource usage for no benefit. Therefore, A is the single best choice for clarity, performance, and robustness.

Final Summary

The AI Roundtable overwhelmingly favored the canonical simplicity of `count(*)` (Option A), with five models identifying it as the only truly optimal choice for PostgreSQL. Sonar Pro acted as the lone dissenter, arguing for Option D by asserting that since a primary key is non-null, `count(id)` is functionally and performance-wise indistinguishable from the wildcard. However, the majority remained unmoved, citing specific engine optimizations like the visibility map that make `count(*)` the technical gold standard.

No agreement after 1 rounds of discussion. Final vote: A and B: 1, A: 5

Strongest Arguments

• A: PostgreSQL optimizes count(*) by using the visibility map to skip all-visible pages, avoiding unnecessary column fetches and allowing the planner to use the smallest available index.
• A and B: Since a primary key is guaranteed to be non-null and unique, count(*) and count(id) produce identical results with negligible performance differences, making both 'best' relative to the redundant deduplication of count(distinct).