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 33C99D1B88E for ; Fri, 4 Jun 2004 19:29:33 -0300 (ADT) 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 68676-02 for ; Fri, 4 Jun 2004 22:29:30 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 711AFD1B8E8 for ; Fri, 4 Jun 2004 19:29:27 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id 007AB76A2A; Fri, 4 Jun 2004 18:29:34 -0400 (EDT) Subject: Re: postgres performance: comparing 2 data centers From: Rod Taylor To: Michael Nonemacher Cc: Postgresql Performance In-Reply-To: References: Content-Type: text/plain Message-Id: <1086388169.67371.94.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Fri, 04 Jun 2004 18:29:29 -0400 Content-Transfer-Encoding: 7bit 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: 200406/40 X-Sequence-Number: 7098 On Fri, 2004-06-04 at 18:07, Michael Nonemacher wrote: > Slight update: > > Thanks for the replies; this is starting to make a little more sense... > > I've managed to track down the root of the problem to a single query on > a single table. I have a query that looks like this: > select count(*) from members where group_id = ? and member_id > > 0; > > The members table contains about 500k rows. It has an index on > (group_id, member_id) and on (member_id, group_id). > > It seems like the statistics are wildly different depending on whether > the last operation on the table was a 'vacuum analyze' or an 'analyze'. Yes, bad stats are causing it to pick a poor plan (might be better in 7.5), but you're giving it too many options (which doesn't help) and using diskspace up unnecessarily. Keep (group_id, member_id) Remove (member_id, group_id) Add (member_id) An index on just member_id is actually going to perform better than member_id, group_id since it has a smaller footprint on the disk. Anytime where both group_id and member_id are in the query, the (group_id, member_id) index will likely be used.