Message-ID: <4ED2BED3.1070303@ringerc.id.au>
Date: Mon, 28 Nov 2011 06:50:59 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:7.0.1) Gecko/20110930 Thunderbird/7.0.1
MIME-Version: 1.0
To: Maxim Boguk <maxim.boguk@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Some question about lazy subquery/procedures execution
	in SELECT ... ORDER BY... LIMIT N queries
References: 
 <CAK-MWwTasteHbxcSN69jKfBbXJmxxUrtH7xL-jaBjEj3_2xfaQ@mail.gmail.com>
	<24774.1322154302@sss.pgh.pa.us>
	<CAK-MWwT5+xaQhydZ0TZo_U+Q=Rb9C2H=m+f4LuVqxkk7BU3CVQ@mail.gmail.com>
In-Reply-To: 
 <CAK-MWwT5+xaQhydZ0TZo_U+Q=Rb9C2H=m+f4LuVqxkk7BU3CVQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11/25/2011 06:53 AM, Maxim Boguk wrote:
> I understand that position.
> However if assumption: " the definition of ORDER BY --- it happens after
> computing the select list, according to the SQL standard"
> is correct,
> then plans like:
>
> postgres=# EXPLAIN ANALYZE SELECT * from test order by _data limit 10
> offset 1000;
>                                                                QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=2884.19..2913.03 rows=10 width=8) (actual
> time=3.584..3.620 rows=10 loops=1)
>     ->  Index Scan using random_key on test  (cost=0.00..2884190.16
> rows=1000000 width=8) (actual time=0.103..3.354 rows=1010 loops=1)
>   Total runtime: 3.663 ms
> (3 rows)
> should not be used at all.


`LIMIT' and `OFFSET' are explicitly defined to compute only that part of 
the SELECT list that is required. If they weren't specifically defined 
with that exception then you'd be right.

LIMIT and OFFSET aren't standard anyway, so Pg can define them to mean 
whatever is most appropriate. The SQL standard is adding new and (as 
usual) painfully clumsily worded features that work like LIMIT and 
OFFSET, but I don't know whether they have the same rules about whether 
execution of functions can be skipped or not.

> And it is not possible to predict in advance where and when you get hit
> by that problem.

That's the biggest problem with statistics- and heuristics-based query 
planners in general, but this does seem to be a particularly difficult case.

Setting a cost on the function call that more accurately reflects how 
expensive it is so PostgreSQL will work harder to avoid calling it might 
help. See 
http://www.postgresql.org/docs/current/static/sql-createfunction.html .

--
Craig Ringer