Re: experiments in query optimization

On Mon, 29 Mar 2010, Robert Haas wrote:

> 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?

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.