Answer a question

I read the slow counting entry in the Postgres Wiki.
For my tables the estimates are quite accurate. For views, however

SELECT reltuples FROM pg_class WHERE relname = 'tbl';

doesn't work and always return 0 records. Is there any way to count or estimate the rows of a view in Postgres other than this?

SELECT COUNT(*) FROM someview; 

ANALYZE did not work either for views (no problem for tables), I just get:

 ANALYZE v_myview;
 WARNING:  skipping "v_myview" --- cannot analyze non-tables or special system tables
 ANALYZE

Answers

The query returns 0 because that's the correct answer.

SELECT reltuples FROM pg_class WHERE relname = 'someview';

A VIEW (unlike Materialized Views) does not contain any rows. Internally, it's an empty table (0 rows) with a ON SELECT DO INSTEAD rule. The manual:

The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

Consequently, ANALYZE is not applicable to views.
The system does not maintain rowcount estimates for views. Depending on the actual view definition you may be able to derive a number from estimates on underlying tables.

A MATERIALIZED VIEW might also be a good solution:

CREATE MATERIALIZED VIEW someview_ct AS SELECT count(*) AS ct FROM someview;

Or base it on the actual view definition directly. Typically considerably cheaper than materializing the complete derived table. It's a snapshot, just like the estimates in pg_class, just more accurate immediately after a REFRESH. You can run the expensive count once and reuse the result until you doubt it's still fresh enough.

Related:

• Fast way to discover the row count of a table in PostgreSQL