Message-ID: <4EB1E14C.4010808@consistentstate.com>
Date: Wed, 02 Nov 2011 18:33:16 -0600
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: two table join just not fast enough.
References: <4EB1A419.1080901@consistentstate.com>
	<29294.1320277982@sss.pgh.pa.us>
In-Reply-To: <29294.1320277982@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks Tom,
And looks like I pasted an older explain plan, which is almost exactly 
the same as the one with 50MB work_mem, except for the hash join 
'buckets' part which used more memory and only one 'bucket' so to speak.

When running with the 50MB work_mem over 1MB work_mem, the query went 
from an average of 190 ms to 169 ms, so it did help some but it wasn't a 
game changer (I even found for this specific query, 6MB of work_mem was 
the most that would actually help me).

I have other plans to try to get this thing running faster, I'll be 
exploring them tomorrow, as well as looking at using an enum type.

- Brian F

On 11/02/2011 05:53 PM, Tom Lane wrote:
> Brian Fehrle<brianf@consistentstate.com>  writes:
>> I've got a query that I need to squeeze as much speed out of as I can.
> Hmm ... are you really sure this is being run with work_mem = 50MB?
> The hash join is getting "batched", which means the executor thinks it's
> working under a memory constraint significantly less than the size of
> the filtered inner relation, which should be no more than a couple
> megabytes according to this.
>
> I'm not sure how much that will save, since the hashjoin seems to be
> reasonably speedy anyway, but there's not much other fat to trim here.
>
> One minor suggestion is to think whether you really need string
> comparisons here or could convert that to use of an enum type.
> String compares ain't cheap, especially not in non-C locales.
>
> 			regards, tom lane