To: Brian Fehrle <brianf@consistentstate.com>
cc: pgsql-performance@postgresql.org
Subject: Re: two table join just not fast enough. 
In-reply-to: <4EB1A419.1080901@consistentstate.com> 
References: <4EB1A419.1080901@consistentstate.com>
Comments: In-reply-to Brian Fehrle <brianf@consistentstate.com>
	message dated "Wed, 02 Nov 2011 14:12:09 -0600"
Date: Wed, 02 Nov 2011 19:53:02 -0400
Message-ID: <29294.1320277982@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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