How to determine whether table has more than N rows quickly?-CodePudding

Table mytable may have 0 .. 150000 rows in different databases.
How to determine whether it has more than approximately 100 rows rapidly?

It looks like this query:

select count(*)>100 from mytable

scans whole table, which takes lot of time. How to speed it up?

The upper bound of interest is not always exactly 100. It can be anything in the range (50, 150).

How to find has table more than 100 /- 50 rows? Maybe statistic database used by query planner or some index count can used?

Using Postgres 12.2
ASP.NET MVC 6 application using EF Core and Npgsql.

CodePudding user response：

How to determine whether it has more than approximately 100 rows rapidly?

Perfect solution for just that:

SELECT count(*) FROM (SELECT FROM mytable LIMIT 101) t;

If you get 101, then the table has more than 100 rows (exact count, not approximated). Else you get the tiny count. Either way, Postgres does not consider more than 101 rows and this will always be fast.
(Obviously, if 150 is your actual upper bound, work with LIMIT 151 instead.)

For other fast ways to count or estimate, see:

Fast way to discover the row count of a table in PostgreSQL
(Including this little trick at the very bottom.)