Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BF52C2E01DC for ; Fri, 7 Mar 2008 21:20:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 85667-01 for ; Fri, 7 Mar 2008 21:19:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from davinci.ethosmedia.com (unknown [209.128.84.227]) by postgresql.org (Postfix) with ESMTP id DCF6A2E00B6 for ; Fri, 7 Mar 2008 21:20:02 -0400 (AST) X-EthosMedia-Virus-Scanned: no infections found Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 15751459; Fri, 07 Mar 2008 17:24:52 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: Why the difference in plans ? Date: Fri, 7 Mar 2008 17:23:24 -0800 User-Agent: KMail/1.8 Cc: Dave Cramer References: <200803060926.38708.josh@agliodbs.com> <5AA2522A-E359-4BA2-871C-5C5EA718F31F@fastcrypt.com> In-Reply-To: <5AA2522A-E359-4BA2-871C-5C5EA718F31F@fastcrypt.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200803071723.25261.josh@agliodbs.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/170 X-Sequence-Number: 29124 Dave, > "user_profile_pkey" PRIMARY KEY, btree (uid) CLUSTER > "user_profile_name_idx" UNIQUE, btree (name varchar_pattern_ops) > "user_profile_name_key" UNIQUE, btree (name) > "user_profile_uploadcode_key" UNIQUE, btree (uploadcode) > "user_profile_active_idx" btree (isactive) > "user_profile_areacode_index" btree (areacode) > "user_profile_gender_idx" btree (gender) You need to change one of the name indexes to a functional index on lower(firstname). That'll speed the query up considerably. I'm still puzzled as to why the index is being used at all in the 2nd query, as it seems very unlikely to work out, but the above is the practical solution to your problem. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco