To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Why query takes soo much time 
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>
Comments: In-reply-to Denis de Bernardy <ddebernardy@yahoo.com>
	message dated "Mon, 16 May 2011 04:16:44 -0700"
Date: Mon, 16 May 2011 10:15:59 -0400
Message-ID: <15163.1305555359@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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