From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Kappler <tkappler@googlemail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with self-join, group by, 100m rows
Date: Wed, 21 Sep 2011 11:02:42 -0400
Message-ID: <23039.1316617362@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOij6AxT+TQOx3_Q_1QFKNvDr5pr-z4zu8FX5nKjUj99bOH5_Q@mail.gmail.com>
References: <CAOij6AxT+TQOx3_Q_1QFKNvDr5pr-z4zu8FX5nKjUj99bOH5_Q@mail.gmail.com>
Thomas Kappler <tkappler@googlemail.com> writes:
> The query we want to do is (with example values):
> select t.cid, count(distinct t1.subject_id)
> from termindex t1, termindex t2
> where t1.cid=20642 and t1.indextype=636 and t2.indextype=636 and
> t2.subject_id=t1.subject_id
> group by t2.cid;
The EXPLAIN output you provided doesn't appear to match this query (in
particular, I don't see the indextype restrictions being checked
anyplace in the plan).
One quick-and-dirty thing that might help is to raise work_mem enough
so that (1) you get a hash aggregation not a sort/group one, and (2)
if there are still any sorts being done, they don't spill to disk.
That will probably be a higher number than would be prudent to install
as a global setting, but you could SET it locally in the current
session before issuing the expensive query.
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: tgl@sss.pgh.pa.us, tkappler@googlemail.com
Subject: Re: Slow query with self-join, group by, 100m rows
In-Reply-To: <23039.1316617362@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