From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Johann Spies <johann.spies@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: query not using index
Date: Sat, 28 Dec 2013 15:31:11 +1300
Message-ID: <52BE37EF.7090109@catalyst.net.nz> (raw)
In-Reply-To: <CAGZ55DRMddpAbyRfDW_A8Hfg2ppmhdcf-JPAko5Rm+JQWh-XPQ@mail.gmail.com>
References: <CAGZ55DTDp1itd74vgySLYURJBRtxGe7_jRdPYLT76cGt1p8hEg@mail.gmail.com>
<8767.1387464535@sss.pgh.pa.us>
<CAGZ55DRMddpAbyRfDW_A8Hfg2ppmhdcf-JPAko5Rm+JQWh-XPQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 23/12/13 21:58, Johann Spies wrote:
>
>
>
> On 19 December 2013 16:48, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Johann Spies <johann.spies@gmail.com
> <mailto:johann.spies@gmail.com>> writes:
> > I would appreciate some help optimising the following query:
>
> It's a mistake to imagine that indexes are going to help much with
> a join of this size. Hash or merge join is going to be a lot better
> than nestloop. What you need to do is make sure those will perform
> as well as possible, and to that end, it'd likely help to raise
> work_mem. I'm not sure if you can sanely put it high enough to
> make the query operate totally in memory --- it looks like you'd
> need work_mem of 500MB or more to prevent any of the sorts or
> hashes from spilling to disk, and keep in mind that this query
> is going to use several times work_mem because there are multiple
> sorts/hashes going on. But if you can transiently dedicate a lot
> of RAM to this query, that should help some. I'd suggest increasing
> work_mem via a SET command in the particular session running this
> query --- you don't want such a high value to be the global default.
>
>
> Thanks Tom. Raising work_mem from 384MB to 512MB made a significant
> difference.
>
> You said "hash or merge join id going to be a lot better than
> nestloop". Is that purely in the hands of the query planner or what can
> I do to get the planner to use that options apart from raising the work_mem?
>
>
You can disable the hash and merge join options by doing:
SET enable_hashjoin=off;
SET enable_mergejoin=off;
before running the query again. Timing it (or EXPLAIN ANALYZE) should
demonstrate if that planner made the right call by choosing hash or
merge in the first place.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: mark.kirkwood@catalyst.net.nz, johann.spies@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: query not using index
In-Reply-To: <52BE37EF.7090109@catalyst.net.nz>
* 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