DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=YEmQ9nXe7rkSJ+/d4ibaSY7Golumh718/aoJNgykF6TEszSDAzcbgJoWrltKLwfSH0
	xo6r+hLbT+d9fAnoWrMuTPwSLHrCIUtlA4nRPy3++l192BfCh8MqJPvbHh8Cg8AErrAA
	nUveFly+siOeGZ/EEIJCZsjUI72iTmXq7+cBY=
MIME-Version: 1.0
In-Reply-To: <alpine.DEB.2.00.1003251457350.13883@orwell.homelinux.org>
References: <alpine.DEB.2.00.1003251457350.13883@orwell.homelinux.org>
Date: Mon, 29 Mar 2010 14:02:03 -0400
Message-ID: <603c8f071003291102g778afc2fvda0fc281454526c9@mail.gmail.com>
Subject: Re: experiments in query optimization
From: Robert Haas <robertmhaas@gmail.com>
To: Faheem Mitha <faheem@email.unc.edu>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Mar 25, 2010 at 3:57 PM, Faheem Mitha <faheem@email.unc.edu> wrote:
>
> Hi everyone,
>
> I've been trying to reduce both memory usage and runtime for a query.
> Comments/suggestions gratefully received. Details are at
>
> http://bulldog.duhs.duke.edu/~faheem/snppy/opt.pdf
>
> See particularly Section 1 - Background and Discussion.
>
> If you want a text version, see
>
> http://bulldog.duhs.duke.edu/~faheem/snppy/opt.tex
>
> For background see
>
> http://bulldog.duhs.duke.edu/~faheem/snppy/diag.pdf (text version
> http://bulldog.duhs.duke.edu/~faheem/snppy/diag.tex) and
> http://bulldog.duhs.duke.edu/~faheem/snppy/snppy.pdf
>
> Please CC any replies to me at the above email address. Thanks.

Didn't you (or someone) post about these queries before?

It's not really too clear to me from reading this what specific
questions you're trying to answer.  One random thought: WHERE
row_number() = 1 is not too efficient.  Try using LIMIT or DISTINCT ON
instead.

If you're concerned about memory usage, try reducing work_mem; you've
probably got it set to something huge.

You might need to create some indices, too.

...Robert