2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Andrus
  • 26,339
  • 60
  • 204
  • 378
  • 4
    Does this answer your question? [Fast way to discover the row count of a table in PostgreSQL](https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql) – OldProgrammer Apr 14 '22 at 20:59
  • You could check `pg_class.reltuples` which is what the query planner uses. –  Apr 14 '22 at 21:02

1 Answers1

2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • you can `SELECT FROM mytable LIMIT 101` if less then return real number of rows. if more then it's just take more empty terminal empty space. – jian Apr 15 '22 at 05:19