Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 553B2B5DBC1 for ; Thu, 12 May 2011 17:46:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 71658-04 for ; Thu, 12 May 2011 20:46:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (mail.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id 756E6B5DBE5 for ; Thu, 12 May 2011 17:46:17 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 6D1844A220A for ; Thu, 12 May 2011 22:46:16 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at nathalia.gransy.com Received: from mail.gransy.com ([127.0.0.1]) by localhost (Nathalka.gransy.com.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Xf-9RjlLx5tD for ; Thu, 12 May 2011 22:46:15 +0200 (CEST) Received: from [192.168.1.221] (ip-94-112-0-189.net.upcbroadband.cz [94.112.0.189]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id D5A604A2200 for ; Thu, 12 May 2011 22:46:15 +0200 (CEST) Message-ID: <4DCC4718.8000409@fuzzy.cz> Date: Thu, 12 May 2011 22:46:16 +0200 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [ADMIN] since when has pg_stat_user_indexes.idx_scan been counting? References: In-Reply-To: X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201105/192 X-Sequence-Number: 43701 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