REGISTER  


How can I get the estimated number of rows of a Postgres table?
This thread belongs to soger.forumgeeks.net


2009-04-25 12:09 GMT   |   #1

I have a pretty big website and on it's admin section I have to present satistics on more tables (how many users, products etc) on the header of every page.
My problem is that these tables are getting pretty big and a select count(*) on them takes more and more time every day.

My solution would be to create a table that counts the number of rows in every other table but I'm afraid it would create too many locks because the tables are updated heavily. That is why I thought I should show the number PGAdmin shows as estimated number of rows, but how can I find out that? I know this estimated number is updated when vacuuming but the autovacuum is turned on and a cronjob is vacuuming every table in every 30 minutes, so those estimated numbers would suffice.

2009-04-25 19:23 GMT   |   #2

Comments: 42

You don't have to worry about the locks because Postgres uses MVCC (Multi Version Concurrency Control). You can find out more about MVCC in here:

MVCC basically means that more copies of a row coexist in one table thus eliminating the need for locks. The only thing you need to worry about that your counter table will occupy more and more disk space even if it contains only one row. You can free space by doing VACUUM FULL but a full vacuum requires exclusive lock.

If you are sure the estimated value is good enough you can get it this way:

select reltuples from pg_class where relname='some_table'

2009-04-26 05:30 GMT   |   #3

This is pretty cool. And simple and fast! I just have one more question. How the hell do you find out stuff like this? PGAdmin does not show the table pg_class so how do know it exists?

2009-04-27 10:15 GMT   |   #4

Comments: 42

With a lot of patience and a lot of googling.

The page http://www.postgresql.org/docs/8.3/interactive/catalog-pg-class.html describes pg_class but I tell you my secret how did I find out about it's existence:
In the postgres config file you change log_statement to all reload the config file then you start pgadmin and watch what it does.
Well, it does very complicated queries but with a little experimenting you can learn a lot, for example this is how you get the list of tables in a database:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

Just don't forget to turn off statement logging when you are done experimenting because your log will grow very fast.

2009-04-29 10:33 GMT   |   #5

Jeeez, PGAdmin does horrible queries