Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 3815C2EBA7B
	for <pgsql-performance@postgresql.org>;
	Thu, 24 Nov 2011 13:05:16 -0400 (AST)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1RTcjb-0004rW-Hd
	for pgsql-performance@postgresql.org; Thu, 24 Nov 2011 17:05:15 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pAOH52YT024775;
	Thu, 24 Nov 2011 12:05:02 -0500 (EST)
To: Maxim Boguk <maxim.boguk@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Some question about lazy subquery/procedures execution in SELECT
	... ORDER BY... LIMIT N queries
In-reply-to: 
 <CAK-MWwTasteHbxcSN69jKfBbXJmxxUrtH7xL-jaBjEj3_2xfaQ@mail.gmail.com>
References: 
 <CAK-MWwTasteHbxcSN69jKfBbXJmxxUrtH7xL-jaBjEj3_2xfaQ@mail.gmail.com>
Comments: In-reply-to Maxim Boguk <maxim.boguk@gmail.com>
	message dated "Thu, 24 Nov 2011 13:56:58 +1100"
Date: Thu, 24 Nov 2011 12:05:02 -0500
Message-ID: <24774.1322154302@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 201111/265
X-Sequence-Number: 45776

Maxim Boguk <maxim.boguk@gmail.com> writes:
> Is here any reason why Postgresql calculates subqueries/storable procedures
> in select list before applying ORDER BY / LIMIT?

Well, that's the definition of ORDER BY --- it happens after computing
the select list, according to the SQL standard.  We try to optimize this
in some cases but you can't really complain when we don't.  Consider
putting the expensive function outside the ORDER BY/LIMIT, ie

select ..., expensive_fn() from (select ... order by ... limit ...) ss;

			regards, tom lane