From: Brian Fehrle <brianf@consistentstate.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: two table join just not fast enough.
Date: Wed, 02 Nov 2011 18:33:16 -0600
Message-ID: <4EB1E14C.4010808@consistentstate.com> (raw)
In-Reply-To: <29294.1320277982@sss.pgh.pa.us>
References: <4EB1A419.1080901@consistentstate.com>
<29294.1320277982@sss.pgh.pa.us>
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
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: brianf@consistentstate.com, tgl@sss.pgh.pa.us
Subject: Re: two table join just not fast enough.
In-Reply-To: <4EB1E14C.4010808@consistentstate.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