How to quickly retrieve estimate number of rows in table

you can do “select count(id) from logs”, for example, but this is slow, because the database engine must check the visibility of each row.

in PostGres, you can do “SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = ‘logs’;” to get an estimate.

but this doesn’t work in CockRoach (obviously)

is there a way to get a quick estimate?

my table currently has 8061251 rows, which took maybe 5 minutes to count.

There’s currently no such estimation functions in cockroach, as we don’t maintain much sql-level statistics about the data.

An estimate of per-table disk space usage per table is available in the admin UI: navigate to the “Databases” tab, then the specific database, then the specific table. This is a fairly accurate size and should return quickly.

However, this does not currently currently offer a rowcount estimate; it’s something we should be able to approximate using the same system we use to compute disk space, but it isn’t currently implemented.

I’ve created an issue to add this estimate to the Admin UI if possible. If this information is desired from the CLI, that will require additional work.

1 Like

it would definitely be required in the CLI. this is a common query people do.

for example, if you are filtering a table, you like to know not just the results, but out of how many rows these results came.