X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id F2BFA9DC9C1 for ; Mon, 30 Jan 2006 18:24:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89793-09 for ; Mon, 30 Jan 2006 18:24:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id CBF199DC97A for ; Mon, 30 Jan 2006 18:24:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0UMOl5r024160; Mon, 30 Jan 2006 17:24:47 -0500 (EST) To: Jeff Frost cc: pgsql-admin@postgresql.org Subject: Re: pg_stat_user_indexes view clarification In-reply-to: References: Comments: In-reply-to Jeff Frost message dated "Mon, 30 Jan 2006 13:22:20 -0800" Date: Mon, 30 Jan 2006 17:24:47 -0500 Message-ID: <24159.1138659887@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.101 required=5 tests=[AWL=0.101] X-Spam-Score: 0.101 X-Spam-Level: X-Archive-Number: 200601/321 X-Sequence-Number: 20409 Jeff Frost writes: > Can someone set me straight on whether the following statements are true in > postgresql-8.1.x and if they aren't true, what queries might I need to run to > find these answers? > The following query shows all indexes which are not used. > select schemaname,relname,indexrelname,idx_tup_read,idx_tup_fetch from > pg_stat_user_indexes where idx_tup_read = 0; It's probably more reliable to look at whether idx_scan is increasing, as idx_tup_read wouldn't increment during a scan that found zero matching rows. > The following query shows all indexes which have differing values between > idx_tup_read and idx_tup_fetch indicating indexes which likely need rebuilt > via REINDEX: > select schemaname,relname,indexrelname,idx_tup_read,idx_tup_fetch from > pg_stat_user_indexes where idx_tup_read != idx_tup_fetch; Uh, no, that does NOT imply a need for REINDEX. In particular, a bitmap indexscan increments idx_tup_read but not idx_tup_fetch --- the heap fetches are counted in the parent table's idx_tup_fetch counter instead. (This is because, in the situation where we are ANDing or ORing multiple indexes in a bitmap scan, assigning responsibility for a heap fetch to any particular index is impractical and likely misleading anyway.) I believe the details of the distinction between idx_tup_read and idx_tup_fetch changed in 8.1, but I don't remember exactly how it worked before. regards, tom lane