Re: Internal Operations on LIMIT & OFFSET clause

am Tue, dem 29.08.2006, um 12:51:27 +0530 mailte Vanitha Jaya folgendes:
> Hi Friends,
>
> I have one doubt in LIMIT & OFFSET clause operation.
> I have a table "test_limit", and it contain,

First of all, you can use EXPLAIN ANALYSE for such tasks!

test=*# explain analyse select * from mira limit 13;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.20 rows=13 width=12) (actual time=0.073..0.146 rows=13 loops=1)
-> Seq Scan on mira (cost=0.00..2311.00 rows=150000 width=12) (actual time=0.068..0.097 rows=13 loops=1)
Total runtime: 0.223 ms
(3 rows)

This is a Seq-Scan for the first 13 records. The table contains 15.000 records.

>
> I also tried ORDER BY clause as bellow.
> SELECT * from test_limit ORDER BY s_no LIMIT 5;

test=*# explain analyse select * from mira order by 1 limit 13;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=17263.70..17263.73 rows=13 width=12) (actual time=1149.554..1149.624 rows=13 loops=1)
-> Sort (cost=17263.70..17638.70 rows=150000 width=12) (actual time=1149.548..1149.574 rows=13 loops=1)
Sort Key: x
-> Seq Scan on mira (cost=0.00..2311.00 rows=150000 width=12) (actual time=0.013..362.187 rows=150000 loops=1)
Total runtime: 1153.545 ms
(5 rows)

This is a komplete seq-scan, than the sort, then the limit.

> But, without ORDER BY clause I don't know how many record processing when
> applying LIMIT clause.

Here, with 8.1, it processed only LIMIT records, see my example and notice the
runtime (0.223 ms versus 1153.545 ms).

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net