Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 303022E00C4 for ; Thu, 21 Feb 2008 17:25:55 -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 48134-04 for ; Thu, 21 Feb 2008 17:25:35 -0400 (AST) Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.179]) by postgresql.org (Postfix) with ESMTP id D7BA42E00C2 for ; Thu, 21 Feb 2008 17:25:38 -0400 (AST) Received: by el-out-1112.google.com with SMTP id r27so200784ele.5 for ; Thu, 21 Feb 2008 13:25:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=gl870Y5tH3UdBrfh0nuhSwULbVyNB58SrzCTYeN4eq0=; b=SxZSM0/Q6dcJRy59dG7D08NfN62JpBkYTdJcniN4s2yaRqzW5qT+KVRMW2yQVTDTgJIiem4kZH+hX/HS+QF3fU8ySQFYX+yCVDYW6puwXV6ZKl2CxQsq0te5xaWf4P1yDL8oaJaMkS8F/dQzuwYlBJKLaywpSZlgDmteVCFnH6o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=hjRxVwfRxa/DbPAje32sBKQIFsPXze9FBKBv2gfYr4Hb7166LzHHEcIQIkaXW/gFCfOVDSE9dMvKcrReuwJKbw9hwQTp/K70SrXNVFWNJNhnSocREE521ibZR8o3zxYGfqOHX64qkfWQnz7c/FLoBUpdLOwkmmGomEpqntsHoDM= Received: by 10.115.110.6 with SMTP id n6mr4439547wam.92.1203629136009; Thu, 21 Feb 2008 13:25:36 -0800 (PST) Received: by 10.114.133.6 with HTTP; Thu, 21 Feb 2008 13:25:35 -0800 (PST) Message-ID: Date: Thu, 21 Feb 2008 15:25:35 -0600 From: "Scott Marlowe" To: "Adonias Malosso" Subject: Re: 4s query want to run faster Cc: pgsql-performance@postgresql.org In-Reply-To: <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/277 X-Sequence-Number: 28819 Well, all the row counts in expected and actual are pretty close. I'm guessing it's as optimized as it's likely to get. you could try mucking about with random_page_cost to force index usage, but indexes are not always a win in pgsql, hence the seq scans etc... If the number of rows returned represents a large percentage of the total number of rows in the table, then a seq scan is generally a win. Note that most all the time being spent in this query is on the Hash Join, not on the seq scans. Also, you should really update to 8.2.6 the latest 8.2 version. Check the release notes for the bugs that were fixed between 8.2.1 and 8.2.6