Thread: Internal Operations on LIMIT & OFFSET clause

Hi Friends,

        I have one doubt in LIMIT & OFFSET clause operation.
I have a table "test_limit",  and it contain,

SELECT * from test_limit;
 s_no |    name
------+-------------
    1 | anbarasu
    8 | egambaram
   12 | jyothi
    6 | mahalakshmi
    4 | maheswari
    2 | manju
    5 | ramkumar
    7 | sangeetha
   11 | sasikala
   10 | thekkamalai
    9 | vivek
   13 | ganeshwari
    3 | anandhi
(13 rows)

Here, I applied LIMIT clause as bellow.
SELECT * from test_limit LIMIT 5;
 s_no |    name
------+-------------
    1 | anbarasu
    8 | egambaram
   12 | jyothi
    6 | mahalakshmi
    4 | maheswari
(5 rows)

In this above query was processed only five records  OR all the 13 record was got and then only 5 record printed.
this is what my doubt.

I tried where clause in above query as bellow.
SELECT * from test_limit where s_no IN (1,2,3,4,5,6,7,8,9) LIMIT 5;
 s_no |    name
------+-------------
    1 | anbarasu
    8 | egambaram
    6 | mahalakshmi
    4 | maheswari
    2 | manju
(5 rows)

In this case It should process up to  records fulfill the requirement.
i.e  atleast it should process 6 records.
My question is it is processed only 6 records (fulfill the requirement) or all (13) the records.

I also tried ORDER BY clause as bellow.
SELECT * from test_limit ORDER BY s_no LIMIT 5;
 s_no |   name
------+-----------
    1 | anbarasu
    2 | manju
    3 | anandhi
    4 | maheswari
    5 | ramkumar
(5 rows)

From this output, I know it is processed all(13) the records and the printed only 5 records.
But, without ORDER BY clause I don't know how many record processing when applying LIMIT clause.

---
Vanitha Jaya

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