Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7CAAC9FB1F4 for ; Tue, 6 Feb 2007 11:45:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 64667-08 for ; Tue, 6 Feb 2007 11:45:26 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 5C3CB9FB1C6 for ; Tue, 6 Feb 2007 11:45:25 -0400 (AST) Received: (qmail 22303 invoked by uid 500); 6 Feb 2007 15:45:08 -0000 Date: Tue, 6 Feb 2007 09:45:08 -0600 From: Bruno Wolff III To: Mark Stosberg Cc: pgsql-performance@postgresql.org Subject: Re: explain analyze output for review (was: optimizing a geo_distance()...) Message-ID: <20070206154508.GA13065@wolff.to> Mail-Followup-To: Bruno Wolff III , Mark Stosberg , pgsql-performance@postgresql.org References: <20070204051644.GA3754@wolff.to> <20070206054011.GA9604@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/65 X-Sequence-Number: 23119 On Tue, Feb 06, 2007 at 09:39:54 -0500, Mark Stosberg wrote: > > I've been investigating partial indexes for the pets table. It has about > 300,000 rows, but only about 10 are "active", and those are the ones we > are care about. Queries are also frequently made on males vs females, dogs vs cats It probably won't pay to make partial indexes on sex or species (at least for the popular ones), as you aren't likely to save enough by eliminating only half the cases to make up for maintaining another index. A partial index for active rows probably does make sense. > or specific ages, and those specific cases seem like possible candidates for partial indexes > as well. I played with that approach some, but had trouble coming up with any thing that > benchmarked faster.