From: Julian <tempura@internode.on.net>
To: pgsql-performance@postgresql.org
Subject: Re: limit is sometimes not pushed in view with order
Date: Sun, 14 Apr 2013 01:36:48 +1000
Message-ID: <51697B90.1000603@internode.on.net> (raw)
In-Reply-To: <5169168D.4000903@ngs.hr>
References: <5169168D.4000903@ngs.hr>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tempura@internode.on.net
Subject: Re: limit is sometimes not pushed in view with order
In-Reply-To: <51697B90.1000603@internode.on.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox