Skip site navigation (1) Skip section navigation (2)

Re: experiments in query optimization

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Faheem Mitha <faheem(at)email(dot)unc(dot)edu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: experiments in query optimization
Date: 2010-03-29 18:02:03
Message-ID: 603c8f071003291102g778afc2fvda0fc281454526c9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Mar 25, 2010 at 3:57 PM, Faheem Mitha <faheem(at)email(dot)unc(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Faheem MithaDate: 2010-03-29 18:31:44
Subject: Re: experiments in query optimization
Previous:From: Andy ColsonDate: 2010-03-29 17:36:04
Subject: Re: Performance regarding LIKE searches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group