I have a table called User, it has column uid, age, etc. First I run \d+ User; Then I can see it tells me information below:
Indexes:
"users_pkey" PRIMARY KEY, btree (uid)
So I know uid is used as index. Then I use
SELECT COUNT(uid) FROM User;
to get number of indexes.
But is there any better way, for example, I can use pg_class, pg_stats ...
First off, user is a reserved word. Don't ever use it as identifier.
By "number of indexes" you probably mean "number of rows".
For counting all rows in a table, an index is only going to be useful, if it is considerably smaller than the table itself (in Postgres 9.2 with index-only scans).
Your query:
SELECT COUNT(uid) FROM tbl;
actually counts how many non-null values of uid can be found in table tbl - which happens to coincide with the total number of (live) rows for columns without NULL values. If you actually want to count rows, use instead:
SELECT COUNT(*) FROM tbl;
Slightly shorter and faster.
If an estimate based on the last ANALYZE run is good enough, you can query the system catalog:
SELECT reltuples FROM pg_class WHERE oid = 'my_schema.my_tbl'::regclass;
Obviously much faster, since only a single row has to be read. Refer to the Postgres Wiki about counting for ways to count rows.
You can get the number of indexes on a table from the catalog:
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = 'tablename'::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
Simply replace the select part with an appropriate count statement to get the number.
(Note: to make psql show this kind of query, run it as psql -E.)
