Message-ID: <51697B90.1000603@internode.on.net>
Date: Sun, 14 Apr 2013 01:36:48 +1000
From: Julian <tempura@internode.on.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.12) Gecko/20130116 Icedove/10.0.12
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: limit is sometimes not pushed in view with order
References: <5169168D.4000903@ngs.hr>
In-Reply-To: <5169168D.4000903@ngs.hr>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 13/04/13 18:25, Rikard Pavelic wrote:
> I was investigating some performance issues and stumbled upon this behavior:
> 
> create table main_table (i serial primary key, data varchar, ord int);
> create view main_view_order as select m.i, m.data, m.ord from main_table m order by m.i desc;
> 
> insert into main_table select i, i::text, i/10 from generate_series(1,1000000) i;
> 
> create index ix_ord on main_table(ord);
> analyze main_table;
> 
> explain analyze select * from main_view_order m where m.ord >= 5000 and m.ord <= 5500 limit 10;
> 
> Limit  (cost=0.00..69.01 rows=10 width=14) (actual time=330.943..330.951 rows=10 loops=1)
>   ->  Index Scan Backward using main_table_pkey on main_table m  (cost=0.00..36389.36 rows=5281 width=14) (actual time=330.937..330.940 rows=10 loops=1)
>         Filter: ((ord >= 5000) AND (ord <= 5500))
> Total runtime: 330.975 ms
> 
> I havent found it on TODO or in archives so I'm wondering if this is a known behavior.
> 
> Regards,
> Rikard
> 
Hi,
Disregard the VIEW for the moment. (its not the issue here).

I wasn't able to get much better than a LIMIT of around 50 after a
SET STATISTICS 1000 on the PK column (i).

julian=# explain analyse select * from main_table m where m.ord >= 5000
and m.ord <= 5500 order by m.i desc limit 49;
                                                                QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=352.73..352.85 rows=49 width=14) (actual time=3.215..3.227
rows=49 loops=1)
   ->  Sort  (cost=352.73..365.23 rows=5000 width=14) (actual
time=3.213..3.217 rows=49 loops=1)
         Sort Key: i
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Index Scan using ix_ord on main_table m  (cost=0.00..187.36
rows=5000 width=14) (actual time=0.025..1.479 rows=5010 loops=1)
               Index Cond: ((ord >= 5000) AND (ord <= 5500))
 Total runtime: 3.252 ms


However, at LIMIT 48 it goes bad:

julian=# explain analyse select * from main_table m where m.ord >= 5000
and m.ord <= 5500 order by m.i desc limit 48;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..349.34 rows=48 width=14) (actual
time=280.158..280.179 rows=48 loops=1)
   ->  Index Scan Backward using main_table_pkey on main_table m
(cost=0.00..36389.36 rows=5000 width=14) (actual time=280.156..280.172
rows=48 loops=1)
         Filter: ((ord >= 5000) AND (ord <= 5500))
         Rows Removed by Filter: 944991
 Total runtime: 280.206 ms


49 rows is pretty good IMO.
But others might want to give some tips, because I don't use LIMIT much.
You might want to consider using CURSORs - Which in this example
would cache the 49 rows and pass the rows you limit (FETCH) more
efficiently.

Regards,
Jules.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance