All about data processing & analytics. Open source database,No-SQL and Hadoop. Discussed issues/solution which I've got during my experience..
Tuesday, March 27, 2012
QUERY TO RETUN MOSTLY USED / ACCESSED TABLE IN POSTGRESQL
Below query will return most accessed table in PostgreSQL database,
SELECT c.relname as table_name, c.reltuples::numeric as no_of_records, c.relpages as no_of_pages,
t.seq_scan as tbl_seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch, t.n_tup_ins, t.n_tup_upd, t.n_tup_del,
COALESCE(t.idx_tup_fetch,0) + COALESCE(t.seq_tup_read,0) as total_read
FROM pg_stat_user_tables as t inner join pg_class as c
ON (t.relid = c.oid)
WHERE c.relkind ='r'
ORDER BY total_read desc
limit 5
it return top 5 mostly used table, if you want this query to return more-then 5 table change the limit 5 to desired No.of table
Subscribe to:
Post Comments (Atom)
Labels
- #agriculture (1)
- #ai (5)
- #pdf (1)
- Big Data (30)
- blogging (3)
- data analytics (5)
- data science (7)
- Deep Learning (4)
- Hadoop (28)
- Hadoop Eco System (27)
- hdfsCommands (4)
- Hive (5)
- IssueSolution (4)
- jobs (3)
- links (1)
- Machine Learning (4)
- mahout (2)
- MapReduce (1)
- MongoDB (6)
- MySQL (4)
- PlpgSQL (8)
- postgres (6)
- PostgreSQL (53)
- R (20)
- RHadoop (2)
- search keywords (1)
- social (2)
- spark (3)
- twitter (6)
- usecase (2)
- visualization (7)
- weka (1)
No comments:
Post a Comment