Re: Some question about lazy subquery/procedures execution in SELECT ... ORDER BY... LIMIT N queries

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.


In realty I was bite by next scenario (that is simplified case):

postgres=# CREATE TABLE test as (select random() as _data from (select * from generate_series(1,1000000)) as t);
SELECT 1000000
postgres=# CREATE INDEX random_key on test(_data);
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# set seq_page_cost to 1;
SET
postgres=# set random_page_cost to 4;
SET
postgres=# set effective_cache_size to '16MB';
SET

Now:
postgres=# EXPLAIN analyze SELECT *,(select pg_sleep(10)) from test order by _data limit 10;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.01..28.85 rows=10 width=8) (actual time=10001.132..10001.198 rows=10 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=10001.076..10001.078 rows=1 loops=1)
   ->  Index Scan using random_key on test  (cost=0.00..2884190.16 rows=1000000 width=8) (actual time=10001.129..10001.188 rows=10 loops=1)
 Total runtime: 10001.252 ms
(5 rows)

Is ok.

postgres=# EXPLAIN analyze SELECT *,(select pg_sleep(10)) from test order by _data limit 10 offset 10000;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=28841.91..28870.76 rows=10 width=8) (actual time=10037.850..10037.871 rows=10 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=10001.040..10001.041 rows=1 loops=1)
   ->  Index Scan using random_key on test  (cost=0.00..2884190.16 rows=1000000 width=8) (actual time=10001.094..10036.022 rows=10010 loops=1)
 Total runtime: 10037.919 ms
(5 rows)

Is still ok.


postgres=# EXPLAIN SELECT *,(select pg_sleep(10)) from test order by _data limit 10 offset 100000;
                                QUERY PLAN
--------------------------------------------------------------------------
 Limit  (cost=102723.94..102723.96 rows=10 width=8)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Sort  (cost=102473.92..104973.92 rows=1000000 width=8)
         Sort Key: _data
         ->  Seq Scan on test  (cost=0.00..14425.00 rows=1000000 width=8)
(6 rows)

Ooops, there project screwed.

And it is not possible to predict in advance where and when you get hit by that problem.
E.g. all usually fast statements with some arguments become slow as a snail once DB switch from index scan to top node sort.

Only way prevent that is always write all queries way you suggested.

Kind Regards,
Maksym




--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.