From: Tom Lane <tgl@sss.pgh.pa.us>
To: Johann Spies <johann.spies@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query not using index
Date: Thu, 19 Dec 2013 09:48:55 -0500
Message-ID: <8767.1387464535@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGZ55DTDp1itd74vgySLYURJBRtxGe7_jRdPYLT76cGt1p8hEg@mail.gmail.com>
References: <CAGZ55DTDp1itd74vgySLYURJBRtxGe7_jRdPYLT76cGt1p8hEg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Johann Spies <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.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, johann.spies@gmail.com
Subject: Re: query not using index
In-Reply-To: <8767.1387464535@sss.pgh.pa.us>
* 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