Re: limit is sometimes not pushed in view with order - Mailing list pgsql-performance

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.940rows=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.