pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInternal Operations on LIMIT & OFFSET clause
2+ messages / 2 participants
[nested] [flat]
* Internal Operations on LIMIT & OFFSET clause
@ 2006-08-29 07:21 Vanitha Jaya <e.vanitha@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Vanitha Jaya @ 2006-08-29 07:21 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Internal Operations on LIMIT & OFFSET clause
@ 2006-08-29 07:38 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Vanitha Jaya <e.vanitha@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: A. Kretschmer @ 2006-08-29 07:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-29 07:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-29 07:21 Internal Operations on LIMIT & OFFSET clause Vanitha Jaya <e.vanitha@gmail.com>
2006-08-29 07:38 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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