X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6DB8B329D86 for ; Thu, 16 Sep 2004 22:37:02 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99563-07 for ; Thu, 16 Sep 2004 21:36:46 +0000 (GMT) Received: from ms-smtp-03.nyroc.rr.com (ms-smtp-03.nyroc.rr.com [24.24.2.57]) by svr1.postgresql.org (Postfix) with ESMTP id 34C3F329DB3 for ; Thu, 16 Sep 2004 22:36:46 +0100 (BST) Received: from jcoene2 (roc-66-66-153-192.rochester.rr.com [66.66.153.192]) by ms-smtp-03.nyroc.rr.com (8.12.10/8.12.10) with ESMTP id i8GLagv2015192; Thu, 16 Sep 2004 17:36:43 -0400 (EDT) Message-Id: <200409162136.i8GLagv2015192@ms-smtp-03.nyroc.rr.com> From: "Jason Coene" To: "'Qing Zhao'" Cc: "'Postgresql Performance'" Subject: Re: indexes make other queries slow! Date: Thu, 16 Sep 2004 17:36:46 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <3C40F514-0826-11D9-B278-000A95AB8896@supplyfx.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcScMzFxiaDg/rezQgS+W+tPMz4wawAAFyvA X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/180 X-Sequence-Number: 8308 > My guess is that all the queries that involves the columns that are > being indexed need to > be rewritten to use the newly created indexes to avoid the performance > issues. The reason > is that REINDEX does not help either. Does it make sense? > Qing, Generally, adding new indexes blindly will hurt performance, not help it. More indexes mean more work during INSERT/UPDATE. That could easily be hampering your performance if you have a high INSERT/UPDATE volume. Run your queries through EXPLAIN ANALYZE to make sure they're using the right indexes. Take a look at the pg_stat_user_indexes table to see what indexes are simply not being used. Jason