"Retrieve exact row counts" preference is very dangerous

[johnthuss]

You can use this template for reporting bugs. Feel free to remove parts that are not relevant, or just write something free-form if you prefer.

What did you do?

By default the preference for "Retrieve exact row counts" is ON, which will issue a COUNT on the table if you select it and view it. This can be VERY BAD if it occurs on a large production database. I have had a DB server crash due to this preference being on by default.

What did you expect to happen?

My recommendation would be to apply some heuristic that will never automatically fetch an exact row count if the approximate row count is above X, where X could be say 100,000. The user can always issue a query to get the exact row count if they want it.

What actually happened?

My database server crashed and my application was down for several minutes.

What software versions are you using?

Postico version: 1.5.7

macOS version: 10.15.1 (selecting "About this Mac" from the Apple menu)

PostgreSQL version: 11.0 (execute the command "SELECT VERSION();" to find out)

[jakob]

I've been aware that the preference is annoying for people who have big tables, but I wasn't aware that a count query could crash a server. The heuristic with approximate row count sounds like a good idea, and together with a button for retrieving exact row counts it would be very useful (see #423 and #442).

[bfelbo]

This would be very useful! Any idea of when it might be implemented?

[webdestroya]

Doing something approximate could be better (and would be much faster)

SELECT n_live_tup FROM pg_stat_user_tables WHERE relname='tablename';

using SELECT COUNT(1) from a table causes it to do a full scan. For huge production systems, this is a painful operation

EXPLAIN SELECT COUNT(1) FROM xxxx;

QUERY PLAN:
Aggregate  (cost=222027202.71..222027202.74 rows=1 width=8)
  ->  Index Only Scan using index_xxxxx on xxxx  (cost=0.71..210151106.71 rows=4750438400 width=0)

😢