5

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Matt Bannert
  • 27,631
  • 38
  • 141
  • 207

1 Answers1

6

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    +1 for the nice explanation. However, I never doubted 0 was correct techically. Still though I wonder whether there's a way to figure out how many rows there are (or were) cause, count(*) does work for views. Are materialized views the only solution? – Matt Bannert Jan 26 '15 at 21:19
  • @MattBannert: Why would `count(*)` not work? It's potentially expensive, but is certainly works. BTW, the cheapest materialized view for the purpose would *be* `CREATE MATERIALIZED VIEW AS SELECT count(*) FROM someview;` (or use the actual view definition). – Erwin Brandstetter Jan 26 '15 at 21:44
  • Oh yes, count does work, but I was looking for an alternative because it is actually expensive in my case. – Matt Bannert Jan 26 '15 at 21:46
  • 1
    @MattBannert: Actually, a MV with the count based on the view is probably the most elegant solution for you. Added a bit to my answer. – Erwin Brandstetter Jan 26 '15 at 21:57
  • Thx makes a lot of sense to me. – Matt Bannert Jan 26 '15 at 21:59