pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mitch Pirtle <mitch.pirtle@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: SQL Performance Guidelines
Date: Thu, 27 Jan 2005 09:50:32 -0500
Message-ID: <330532b605012706502e298c77@mail.gmail.com> (raw)
In-Reply-To: <CA5A792A-7039-11D9-9A75-000A957659CC@spy.net>
References: <A3FF4275060B76459B5C08A64AE330C869CE@twmail.esncc.com>
	<CA5A792A-7039-11D9-9A75-000A957659CC@spy.net>

On Thu, 27 Jan 2005 00:02:29 -0800, Dustin Sallings <dustin@spy.net> 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



view thread (5+ messages)  latest in thread

Message-ID: <330532b605012706502e298c77@mail.gmail.com>
Permalink:  ../330532b605012706502e298c77@mail.gmail.com/
Also on:    postgresql.org/message-id/330532b605012706502e298c77@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mitch.pirtle@gmail.com
  Subject: Re: SQL Performance Guidelines
  In-Reply-To: <330532b605012706502e298c77@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox