Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group