Re: experiments in query optimization
От | Faheem Mitha |
---|---|
Тема | Re: experiments in query optimization |
Дата | |
Msg-id | alpine.DEB.2.00.1003292349490.13883@orwell.homelinux.org обсуждение исходный текст |
Ответ на | Re: experiments in query optimization (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: experiments in query optimization
(Robert Haas <robertmhaas@gmail.com>)
Re: experiments in query optimization ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Список | pgsql-performance |
On Mon, 29 Mar 2010, Robert Haas wrote: > 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? 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 по дате отправления: