Timescaledb: Provide a way to get approximate row count for hypertable

[viragkothari]

Below command for regular tables in postgres
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = <table_name>
select count(*) works for an exact count but it takes a longer time for table with huge no of rows

[fvannee]

The following query provides the information you're looking for.

SELECT h.schema_name,
    h.table_name,
    h.id AS table_id,
    h.associated_table_prefix,
    row_estimate.row_estimate
   FROM _timescaledb_catalog.hypertable h
     CROSS JOIN LATERAL ( SELECT sum(cl.reltuples) AS row_estimate
           FROM _timescaledb_catalog.chunk c
             JOIN pg_class cl ON cl.relname = c.table_name
          WHERE c.hypertable_id = h.id
          GROUP BY h.schema_name, h.table_name) row_estimate
ORDER BY schema_name, table_name;

[viragkothari]

Works great, Thanks!

[viragkothari]

Would be good to add to docs.

[fvannee]

Agreed

[mfreed]

Re-opening issue as a enhancement, as we could expose this as an easier utility function to users.

[RobAtticus]

This has been merged in and will be in the upcoming 0.10.0 release (sometime in the next few weeks).

Thanks all!

[solugebefola]

The SQL statement has also been added to the current version of the docs(http://docs.timescale.com/v0.9/using-timescaledb/reading-data#approximate-row-count)

[jflambert]

Unfortunately this won't work with compressed tables.

[sarfata]

The SQL statement has also been added to the current version of the docs http://docs.timescale.com/v0.9/using-timescaledb/reading-data#approximate-row-count

I did not see the function in this version of the doc.

But it is available in the most recent doc: hypertable_approximate_row_count