Re: experiments in query optimization

From: Faheem Mitha
Subject: Re: experiments in query optimization
Date: ,
Msg-id: alpine.DEB.2.00.1003292349490.13883@orwell.homelinux.org
(view: Whole thread, Raw)
In response to: Re: experiments in query optimization  (Robert Haas)
Responses: Re: experiments in query optimization  (Robert Haas)
Re: experiments in query optimization  ("Kevin Grittner")
List: pgsql-performance

Tree view

experiments in query optimization  (Faheem Mitha, )
 Re: experiments in query optimization  (Robert Haas, )
  Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  (Robert Haas, )
    Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  ("Kevin Grittner", )
    Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Robert Haas, )
      Re: experiments in query optimization  (Faheem Mitha, )
       Re: experiments in query optimization  (Robert Haas, )
       Re: experiments in query optimization  (Faheem Mitha, )
        Re: experiments in query optimization  (Eliot Gable, )
         Re: experiments in query optimization  (Faheem Mitha, )
          Re: experiments in query optimization  (Robert Haas, )
           Re: experiments in query optimization  (Faheem Mitha, )
            Re: experiments in query optimization  (Eliot Gable, )
             Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Matthew Wakeling, )
      Re: experiments in query optimization  (Faheem Mitha, )
    Re: experiments in query optimization  (Faheem Mitha, )


On Mon, 29 Mar 2010, Robert Haas wrote:

> On Thu, Mar 25, 2010 at 3:57 PM, Faheem Mitha <> 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?

I did write to the list about an earlier version of these queries, yes. In
fact you replied to that message.

> It's not really too clear to me from reading this what specific
> questions you're trying to answer.

Quote from opt.{tex/pdf}, Section 1:

"If I have to I can use Section~\ref{ped_hybrid} and
Section~\ref{tped_hybrid}, but I am left wondering why I get the
performance I do out of the earlier versions. Specifically, why is
Section~\ref{ped_bigjoin} so much slower than Section~\ref{ped_trunc}, and
why does the memory usage in Section~\ref{ped_phenoout} blow up relative
to Section~\ref{ped_bigjoin} and Section~\ref{ped_trunc}?"

> One random thought: WHERE row_number() = 1 is not too efficient.
> Try using LIMIT or DISTINCT ON instead.

Possibly. However, the CTE that uses

WHERE row_number() = 1

doesn't dominate the runtime or memory usage, so I'm not too concerned
about it.

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

work_mem = 1 GB (see diag.{tex/pdf}).

The point isn't that I'm using so much memory. Again, my question is, why
are these changes affecting memory usage so drastically?

> You might need to create some indices, too.

Ok. To what purpose? This query picks up everything from the tables and
the planner does table scans, so conventional wisdom and indeed my
experience, says that indexes are not going to be so useful.

                                                         Regards, Faheem.


pgsql-performance by date:

From: Faheem Mitha
Date:
Subject: Re: experiments in query optimization
From: Josh Berkus
Date:
Subject: Re: why does swap not recover?