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 A6435D1B51E for ; Mon, 27 Oct 2003 16:32:08 +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 83843-01 for ; Mon, 27 Oct 2003 12:31:36 -0400 (AST) Received: from pass.bivio.com (pass.bivio.com [216.87.82.238]) by svr1.postgresql.org (Postfix) with ESMTP id 3143AD1B4FE for ; Mon, 27 Oct 2003 12:31:32 -0400 (AST) Received: from LIFT.bivio.com (lift.bivio.com [216.87.82.233]) by pass.bivio.com (8.9.3/8.9.3) with ESMTP id JAB12933 for ; Mon, 27 Oct 2003 09:31:15 -0700 From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16285.17811.994000.543635@gargle.gargle.HOWL> Date: Mon, 27 Oct 2003 09:19:31 -0700 To: pgsql-performance@postgresql.org Subject: Re: vacuum locking In-Reply-To: <878ynaupgu.fsf@stark.dyndns.tv> 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> X-Mailer: VM 6.96 under Emacs 21.1.1 Organization: bivio Software Artisans, Inc. X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/679 X-Sequence-Number: 4427 Greg Stark writes: > Sorry I was unclear. By "usual case" I meant reading, as opposed to updates. > The size of the on-disk representation turns out to be a major determinant in > a lot of database applications, since the dominant resource is i/o bandwidth. > Try doing a fresh import of a large table with pctfree 0 pctuse 100 and > compare how long a select takes on it compared to the original table. BTW, I greatly appreciate your support on this stuff. This list is a fantastic resource. I think we agree. The question is what is the workload. On tables without updates, postgres will be fast enough. However, postgres is slow on tables with updates afaict. I think of OLTP as a system with updates. One can do DSS on an OLTP database with Oracle, at least it seems to work for one of our projects. > FIrstly, that type of query will be faster in 7.4 due to implementing a new > method for doing groups called hash aggregates. We'll be trying it as soon as it is out. > Secondly you could try raising sort_mem. Postgres can't know how much memory > it really has before it swaps, so there's a parameter to tell it. And swapping > would be much worse than doing disk sorts. It is at 8000. This is probably as high as I can go with multiple postmasters. The sort area is shared in Oracle (I think :-) in the UGA. > You can raise sort_mem to tell it how much memory it's allowed to > use before it goes to disk sorts. You can even use ALTER SESSION to > raise it in a few DSS sessions but leave it low the many OLTP > sessions. If it's high in OLTP sessions then you could quickly hit > swap when they all happen to decide to use the maximum amount at the > same time. But then you don't want to be doing big sorts in OLTP > sessions anyways. This is a web app. I can't control what the user wants to do. Sometimes they update data, and other times they simply look at it. 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. > Unfortunately there's no way to tell how much memory it thinks it's > going to use. I used to use a script to monitor the pgsql_tmp > directory in the database to watch for usage. I don't have to. The queries that run slow are hitting disk. Anything that takes a minute has to be writing to disk. > Well, first of all it doesn't really because you said to group by t2.name not > f1. You might expect it to at least optimize something like this: I put f2 in the group by, and it doesn't matter. That's the point. It's the on-disk sort before the aggregate that's killing the query. > but even then I don't think it actually is capable of using foreign keys as a > hint like that. I don't think Oracle does either actually, but I'm not sure. I'll be finding out this week. > 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. Rob