X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AEC60D1B4E8 for ; Mon, 27 Oct 2003 17:54:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95644-07 for ; Mon, 27 Oct 2003 13:53:57 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id C6D6DD1B515 for ; Mon, 27 Oct 2003 13:53:56 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 78C5E369EE; Mon, 27 Oct 2003 12:53:56 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AEBYy-0005Hr-00; Mon, 27 Oct 2003 12:53:56 -0500 To: Rob Nagler Cc: pgsql-performance@postgresql.org Subject: Re: vacuum locking References: <16272.318.870000.93584@gargle.gargle.HOWL> <16279.4988.496000.539525@gargle.gargle.HOWL> <200310230814.56738.mweilguni@sime.com> <29123.1066915615@sss.pgh.pa.us> <16279.61590.158000.290397@gargle.gargle.HOWL> <87vfqeveab.fsf@stark.dyndns.tv> <16281.45354.209000.280418@gargle.gargle.HOWL> <878ynaupgu.fsf@stark.dyndns.tv> <16285.17811.994000.543635@gargle.gargle.HOWL> In-Reply-To: <16285.17811.994000.543635@gargle.gargle.HOWL> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Oct 2003 12:53:56 -0500 Message-ID: <87d6ciy3bf.fsf@stark.dyndns.tv> Lines: 42 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/684 X-Sequence-Number: 4432 Rob Nagler writes: > I didn't find ALTER SESSION for postgres (isn't that Oracle?), so I > set sort_mem in the conf file to 512000, restarted postrgres. Reran > the simpler query (no name) 3 times, and it was still 27 secs. Sorry, I don't know how that bubbled up from the depths of my Oracle memory. In postgres it's just "SET" db=> set sort_mem = 512000; SET > > To convince it to do the right thing you would have to do either: > > > > SELECT a, t2.name > > FROM (SELECT avg(f1),f2 FROM t1 GROUP BY f2) AS t1 > > JOIN t2 USING (f2) > > > > Or use a subquery: > > > > SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2) > > FROM t1 > > GROUP BY f2 > > This doesn't solve the problem. It's the GROUP BY that is doing the > wrong thing. It's grouping, then aggregating. But at least in the form above it will consider using an index on f2, and it will consider using indexes on t1 and t2 to do the join. It's unlikely to go ahead and use the indexes though because normally sorting is faster than using the index when scanning the whole table. You should compare the "explain analyze" results for the original query and these two. And check the results with "set enable_seqscan = off" as well. I suspect you'll find your original query uses sequential scans even when they're disabled because it has no alternative. With the two above it can use indexes but I suspect you'll find they actually take longer than the sequential scan and sort -- especially if you have sort_mem set large enough. -- greg