From: Robert Haas <robertmhaas@gmail.com>
To: Faheem Mitha <faheem@email.unc.edu>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: experiments in query optimization
Date: Tue, 30 Mar 2010 15:59:46 -0400
Message-ID: <603c8f071003301259u140d8556vc54ff46855013bd3@mail.gmail.com> (raw)
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>
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 when
> 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. Why 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 join.
...Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, faheem@email.unc.edu, Kevin.Grittner@wicourts.gov
Subject: Re: experiments in query optimization
In-Reply-To: <603c8f071003301259u140d8556vc54ff46855013bd3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox