Re: [ADMIN] [PERFORM] since when has pg_stat_user_indexes.idx_scan been counting?
It's not synchronous at all. The clients create a temporary file for the statistics collector and move on. The actual statistics don't get updated until the statistics collector decides enough time has passed to bother, which defaults to at most every 500ms. Really? I thought the clients send the updates using a socket, at least that's what I see in backend/postmaster/pgstat.c (e.g. in pgstat_send_bgwriter where the data are sent, and in PgstatCollectorMain where it's read from the socket and applied). But no matter how exactly this works, this kind of stats has nothing to do with ANALYZe - it's asynchronously updated every time you run a query. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [ADMIN] [PERFORM] since when has pg_stat_user_indexes.idx_scan been counting?
Then, are the index scans counted in a memory variable and written at analyze time? On Thu, May 12, 2011 at 8:22 PM, raghu ram raghuchenn...@gmail.com wrote: Analyze activity will update the statistics of each catalog table. --Raghu Ram -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [ADMIN] [PERFORM] since when has pg_stat_user_indexes.idx_scan been counting?
On Thu, May 12, 2011 at 9:09 PM, Willy-Bas Loos willy...@gmail.com wrote: Hi, We have some indexes that don't seem to be used at all. I'd like to know since when they have not been used. That is, the time when postgres started counting to reach the number that is in pg_stat_user_indexes.idx_scan Is there a way to retrieve that from the database ? Analyze activity will update the statistics of each catalog table. pg_postmaster_start_time -- Retrieves the Postmaster [ PostgreSQL Instance] start time postgres=# select pg_postmaster_start_time(); --Raghu Ram
Re: [ADMIN] [PERFORM] since when has pg_stat_user_indexes.idx_scan been counting?
Dne 12.5.2011 22:03, Willy-Bas Loos napsal(a): Then, are the index scans counted in a memory variable and written at analyze time? No, I believe raghu mixed two things - stats used by the planner and stats about access to the data (how many tuples were read using an index, etc.) Stats for the planner are stored in pg_class/pg_statistic/pg_stats catalogs and are updated by ANALYZE (either manual or automatic). This is what raghu refered to, but these stats are completely useless when looking for unused indexes. Stats about access to the data (index/seq scans, cache hit ratio etc.) are stored in pg_stat_* and pg_statio_* catalogs, and are updated after running each query. AFAIK it's not a synchronous process, but when a backend finishes a query, it sends the stats to the postmaster (and postmaster updates the catalogs). Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
