DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=A0ceOWUZRMZpPOcur+ietGSNuods+cbU1yzX1rWpUKqVkxRvjIah3zpESStBi1z30O
	deaTD2E13tjIOJXxlLHRFsws9vEMqEo8B0RC8NlRwbnOr2dfl6lam+vCDrHpJos/K1ig
	htk7ub6WEn8Je0PmrNZr9VJmBxX9/bTW50qOY=
MIME-Version: 1.0
In-Reply-To: <alpine.DEB.2.00.1003302151030.13883@orwell.homelinux.org>
References: <alpine.DEB.2.00.1003251457350.13883@orwell.homelinux.org>
	<603c8f071003291102g778afc2fvda0fc281454526c9@mail.gmail.com>
	<alpine.DEB.2.00.1003292349490.13883@orwell.homelinux.org>
	<4BB1DB9A020000250003020E@gw.wicourts.gov>
	<alpine.DEB.2.00.1003302151030.13883@orwell.homelinux.org>
Date: Tue, 30 Mar 2010 15:59:46 -0400
Message-ID: <603c8f071003301259u140d8556vc54ff46855013bd3@mail.gmail.com>
Subject: Re: experiments in query optimization
From: Robert Haas <robertmhaas@gmail.com>
To: Faheem Mitha <faheem@email.unc.edu>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 30, 2010 at 12:30 PM, Faheem Mitha <faheem@email.unc.edu> wrote=
:
> Sure, but define sane setting, please. I guess part of the point is that =
I'm
> trying to keep memory low, and it seems this is not part of the planner's
> priorities. That it, it does not take memory usage into consideration whe=
n
> choosing a plan. If that it wrong, let me know, but that is my
> understanding.

I don't understand quite why you're confused here.  We've already
explained to you that the planner will not employ a plan that uses
more than the amount of memory defined by work_mem for each sort or
hash.

Typical settings for work_mem are between 1MB and 64MB.  1GB is enormous.

>>>> 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.
>>
>> There are situations where scanning the entire table to build up a
>> hash table is more expensive than using an index. =A0Why not test it?
>
> Certainly, but I don't know what you and Robert have in mind, and I'm not
> experienced enough to make an educated guess. I'm open to specific
> suggestions.

Try creating an index on geno on the columns that are being used for the jo=
in.

...Robert