COUNT(*) vs COUNT(1) vs COUNT(column)

COUNT(*) counts rows. COUNT(1) also counts every row, because the constant 1 is never NULL. COUNT(column) skips NULLs in that column.

WITH sample(value) AS (VALUES (10), (NULL), (10))
SELECT COUNT(*) AS rows,
       COUNT(1) AS ones,
       COUNT(value) AS non_null,
       COUNT(DISTINCT value) AS unique_values
FROM sample;
-- 3, 3, 2, 1

Pitfall: COUNT(1) isn't a magic way to speed up a query. In a LEFT JOIN, to count matched records I use e.g. COUNT(right_table.id), provided that identifier is NOT NULL.