Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id DB9499FCB78 for ; Thu, 8 Nov 2007 10:50:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 42947-04 for ; Thu, 8 Nov 2007 10:49:50 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 108549FCB7A for ; Thu, 8 Nov 2007 10:49:43 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so120082nfh for ; Thu, 08 Nov 2007 06:49:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=aQP+IeIgopGIVZD5s9amIPM+VBOQjvCpkDq/Ehq7ywU=; b=rC6hjn/y8dDq8SDTa4QwB/SzFVTwywKctUI2hduKNAY8ACPVY4z4fy+IyV82/9bk7ZmUYln4fTVtx2+IdoBKYR7WIpSYk2M57Icv5DnhZznFyO/501IBy5FsvkejzoeCI7em4X9urV0AUXwOTDCL8T7rGY6BongCFnX7TdpDI5M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=sBl+6vXOs+DXX94mWlbIK+eeQeHjdoFnxagPUNG6/AoOrAOMqaa/KCzMYoLfw78zCNrt8KoNjrQMQgZN0FWu/6Be7eZYswlvjYRYt1LfBcXopLGjM+pdH9P0Bbu6xUlyKUlhZQ0fRXGrA4Tun7r8boaTlPhoxI1BradPcl0ss1U= Received: by 10.78.201.10 with SMTP id y10mr760541huf.1194533381129; Thu, 08 Nov 2007 06:49:41 -0800 (PST) Received: by 10.78.197.6 with HTTP; Thu, 8 Nov 2007 06:49:41 -0800 (PST) Message-ID: <1d219a6f0711080649w22a1eec6j69cab5ea5e5bc74b@mail.gmail.com> Date: Thu, 8 Nov 2007 09:49:41 -0500 From: "Chris Hoover" To: "PGSQL Performance" Subject: Help understanding stat numbers MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/128 X-Sequence-Number: 27543 I have a database where I dropped all indexes on a table last night and built a new set of indexes. The goal is to try and let the database have fewer indexes and use them more. I removed a bunch of indexes that were surviving from our 7.3 days where functionality will now be covered by 8.1's use of multiple indexes.. Anyway, except for the primary key, all indexes were dropped and then the new indexes where created. However, I am confused by what the pg_stat_user_indexes and pg_statio_users_indexes are telling me. Which one is correct. pg_stat_user_indexes is reporting this: "indexrelname","idx_scan",'idx_tup_read","idx_tup_fetch" "clmhdr_pkey";1583576;1577152;1577027 "hdr_clm_status_partial_idx";5243;6999857;372251 "hdr_create_dt_idx";1010;1420708;3656 "hdr_user_id_idx";71;928074;918439 "hdr_pat_cntl_nbr_idx";14;42;29 "hdr_clm_type_idx";1;673982;0 "hdr_process_dt_idx";1;22050;0 "erb_hdr_create_dt_idx";0;0;0 "erb_hdr_process_dt_idx";0;0;0 "erb_hdr_stmt_from_dt_idx";0;0;0 "erb_hdr_stmt_thru_dt_idx";0;0;0 "erb_hdr_transmit_dt_idx";0;0;0 "hdr_accepted_dt_idx";0;0;0 "hdr_asc_resp_rpt_cd_idx";0;0;0 "hdr_bill_type_idx";0;0;0 "hdr_fss_clm_status_idx";0;0;0 "hdr_fss_process_dt_idx";0;0;0 "hdr_submit_mode_idx";0;0;0 "patient_name_idx";0;0;0 "statement_date_idx";0;0;0 pg_statio_user_indexes is reporting: "indexrelname","idx_blks_read","idx_blks_hit" "hdr_clm_status_partial_idx";182;59455 "clmhdr_pkey";115382;6540557 "erb_hdr_process_dt_idx";7943;32679 "erb_hdr_create_dt_idx";8000;32042 "erb_hdr_transmit_dt_idx";7953;31511 "erb_hdr_stmt_thru_dt_idx";8667;30924 "hdr_create_dt_idx";11988;42617 "erb_hdr_stmt_from_dt_idx";8632;30173 "hdr_fss_clm_status_idx";9920;32774 "hdr_bill_type_idx";9949;32730 "hdr_asc_resp_rpt_cd_idx";9916;32387 "hdr_clm_type_idx";11777;33130 "hdr_fss_process_dt_idx";11891;33423 "hdr_accepted_dt_idx";11913;32876 "hdr_process_dt_idx";11976;33049 "hdr_submit_mode_idx";13815;32932 "hdr_user_id_idx";17372;34188 "hdr_pat_cntl_nbr_idx";15061;29137 "statement_date_idx";18838;29834 "patient_name_idx";21619;26182 If there has been no scans on an index (as according to pg_stat_user_indexes), why is pg_statio_user_indexes showing non 0 values in idx_blks_hit/read? Please help me understand this apparent contradiction. Thanks, Chris PG 8.1.3