Re: Internal Operations on LIMIT & OFFSET clause

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Internal Operations on LIMIT & OFFSET clause
Date: 2006-08-29 07:38:11
Message-ID: 20060829073811.GA28883@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Willo van der Merwe 2006-08-29 13:52:50 PostgreSQL performance issues
Previous Message Vanitha Jaya 2006-08-29 07:21:27 Internal Operations on LIMIT & OFFSET clause