From: Robert Haas <robertmhaas@gmail.com>
To: Faheem Mitha <faheem@email.unc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: experiments in query optimization
Date: Mon, 29 Mar 2010 14:02:03 -0400
Message-ID: <603c8f071003291102g778afc2fvda0fc281454526c9@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.1003251457350.13883@orwell.homelinux.org>
References: <alpine.DEB.2.00.1003251457350.13883@orwell.homelinux.org>
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
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: robertmhaas@gmail.com, faheem@email.unc.edu
Subject: Re: experiments in query optimization
In-Reply-To: <603c8f071003291102g778afc2fvda0fc281454526c9@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