From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why query takes soo much time
Date: Mon, 16 May 2011 10:15:59 -0400
Message-ID: <15163.1305555359@sss.pgh.pa.us> (raw)
In-Reply-To: <630076.87313.qm@web112408.mail.gq1.yahoo.com>
References: <4DD0B8AD.3020906@orkash.com>
<4DD0F5A3.1060007@postnewspapers.com.au>
<630076.87313.qm@web112408.mail.gq1.yahoo.com>
Denis de Bernardy <ddebernardy@yahoo.com> writes:
> An alternative plan could have been to hash join the tables together,
> to sort the result set, and to apply the limit/offset on the resulting
> set.
Indeed. I rather wonder why the planner didn't do that to start with.
This plan looks to me like it might be suffering from insufficient
work_mem to allow use of a hash join. Or possibly the OP changed some
of the cost_xxx or enable_xxx settings in a misguided attempt to force
it to use indexes instead. As a rule of thumb, whole-table joins
probably ought not be using nestloop plans, and that frequently means
that indexes are worthless for them.
But in any case, as Craig noted, the real elephant in the room is the
huge OFFSET value. It seems likely that this query is not standing
alone but is meant as one of a series that's supposed to provide
paginated output, and if so the total cost of the series is just going
to be impossible no matter what. The OP needs to think about using a
cursor or some such to avoid repeating most of the work each time.
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
Subject: Re: Why query takes soo much time
In-Reply-To: <15163.1305555359@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