Fast Estimated Postgres Count

Question

I query my database (Postgres 8.4) using the following:

SELECT COUNT(*) FROM table WHERE indexed_varchar LIKE 'bar%'; 

The complexity of this is O (N), because Postgres has to count every line. Postgres 9.2 only has index checks, but updating, unfortunately, is not an option.

However, getting the exact number of rows seems redundant because I only need to know which of the following three cases is true:

• The query does not return rows.
• The query returns a single row.
• A query returns two or more rows.

So I don’t need to know that the query returns 10 421 rows, it just returns more than two.

I know how to handle the first two cases:

 SELECT EXISTS (SELECT COUNT(*) FROM table WHERE indexed_varchar LIKE 'bar%'); 

Which will return true if one or more rows exist, and false does not.

Any ideas on how to expand this to make effective use of all three cases?

Answer 1

 SELECT COUNT(*) FROM ( SELECT * FROM table WHERE indexed_varchar LIKE 'bar%' LIMIT 2 ) t; 

Answer 2

It should be simple. You can use LIMIT to do what you want and return data (count) using the CASE statement.

 SELECT CASE WHEN c = 2 THEN 'more than one' ELSE CAST(c AS TEXT) END FROM ( SELECT COUNT(*) AS c FROM ( SELECT 1 AS c FROM table WHERE indexed_varchar LIKE 'bar%' LIMIT 2 ) t ) v