X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 67331D1B4F2 for ; Fri, 10 Oct 2003 15:49:34 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 40547-04 for ; Fri, 10 Oct 2003 12:48:48 -0300 (ADT) Received: from pass.bivio.com (pass.bivio.com [216.87.82.238]) by svr1.postgresql.org (Postfix) with ESMTP id 030AED1B537 for ; Fri, 10 Oct 2003 12:48:42 -0300 (ADT) 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 JAB18236; Fri, 10 Oct 2003 09:48:35 -0600 From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16262.54365.65000.672148@gargle.gargle.HOWL> Date: Fri, 10 Oct 2003 09:46:37 -0600 To: Postgresql Performance Subject: Re: Speeding up Aggregates In-Reply-To: <874qyh8zoo.fsf@stark.dyndns.tv> References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> <20031003215347.GQ87525@rlx11.zapatec.com> <1065219028.91586.8.camel@jester> <87llrvbx0f.fsf@stark.dyndns.tv> <20031008181819.GJ2979@rlx11.zapatec.com> <87ekxm9fjf.fsf@stark.dyndns.tv> <20031010004446.GN2979@rlx11.zapatec.com> <20031010013522.GB18596@wolff.to> <20031010035526.GT2979@rlx11.zapatec.com> <874qyh8zoo.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/339 X-Sequence-Number: 4087 Greg Stark writes: > Call it a wishlist bug. The problem is it would be a hard feature to > implement properly. And none of the people paid to work on postgres > by various companies seem to have this on their to-do lists. So > don't expect it in the near future. We are using Postgres heavily, and we should be able to find some time and/or funding to help. We're becoming more and more frustrated with the discontinuous behaviour of the planner. It seems every complex query we have these days needs some "hint" like "ORDER BY foo DESC LIMIT 1" to make it run on the order of seconds, not minutes. We usually figure out a way to write the query so the planner does the right thing, and pushes the discontinuity out far enough that the user doesn't see it. However, it takes a lot of work, and it seems to me that work would be put to better use improving the planner than improving our knowledge of how to get the planner to do the right thing by coding the SQL in some unusual way. Please allow me to go out on a limb here. I know that Tom is philosophically opposed to planner hints. However, we do have a problem that the planner is never going to be smart enough. This leaves the SQL coder the only option of collecting a bag of (non-portable) SQL tricks. I saw what the best SQL coders did at Tandem, and frankly, it's scary. Being at Tandem, the SQL coders also had the option (if they could argue their case strong enough) of adding a new rule to the optimizer. This doesn't solve the problem for outsiders no matter how good they are at SQL. Would it be possible to extend the planner with a pattern matching language? It would formalize what it is doing already, and would allow outsiders to teach the planner about idiosyncrasies without changing the SQL. It would be like a style sheet in Latex (or Scribe :-) if you are familiar with these typesetting languages. Comments? Rob