X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 976FE3A5EF6 for ; Thu, 27 Jan 2005 14:50:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68708-02 for ; Thu, 27 Jan 2005 14:50:35 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id 962373A5F12 for ; Thu, 27 Jan 2005 14:50:37 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 40so262146wri for ; Thu, 27 Jan 2005 06:50:32 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=sswD8VyVYHnl10KPP7F3/ODpv9hOG/3GAosXs9bhPUDrazR1JALeGw9VyAR6WOr5h4dPsUWl5glGFo0u8djAnSyJy1nMQtXuRpZ7qj+OFWJWLCgujmDVIZxwAtozeHDVYZPEuTj6g3I+ThK2l4WtuNnuXgWmjZfFSPexRrVIDPI= Received: by 10.54.20.36 with SMTP id 36mr79381wrt; Thu, 27 Jan 2005 06:50:32 -0800 (PST) Received: by 10.54.59.22 with HTTP; Thu, 27 Jan 2005 06:50:32 -0800 (PST) Message-ID: <330532b605012706502e298c77@mail.gmail.com> Date: Thu, 27 Jan 2005 09:50:32 -0500 From: Mitch Pirtle Reply-To: Mitch Pirtle To: pgsql-performance@postgresql.org Subject: Re: SQL Performance Guidelines In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/537 X-Sequence-Number: 10227 On Thu, 27 Jan 2005 00:02:29 -0800, Dustin Sallings wrote: > > On Jan 26, 2005, at 10:27, Van Ingen, Lane wrote: > > > Clarification: I am talking about SQL coding practices in Postgres > > (how to write queries for best > > results), not tuning-related considerations (although that would be > > welcomed too). > > Your question is a bit too vague. At this point in your development, > all that really can be said is to understand relational database > concepts in general, and use explain a lot when developing queries. > (Oh, and don't forget to analyze before asking specific questions). I disagree - there are plenty of tricks that are PostgreSQL only, and many people on this list have that knowledge but it is not documented anywhere, or is hidden within thousands of mailing list posts. For example, IIRC when joining an integer column with a SERIAL column, you must expicitly cast it as an integer or the planner will not use the indexes, right? (This is a guess, as I remember reading something like this and thinking, "How in the world is someone supposed to figure that out, even with EXPLAIN?") There is another thread about how a query using a WHERE NOT NULL clause is faster than one without. These things are PostgreSQL specific, and documenting them would go a long way towards educating the switchover crowd. The closest thing I have seen to this is the PostgreSQL Gotchas page: http://sql-info.de/postgresql/postgres-gotchas.html HTH, -- Mitch