Re: Internal Operations on LIMIT & OFFSET clause - Mailing list pgsql-performance

From A. Kretschmer
Subject Re: Internal Operations on LIMIT & OFFSET clause
Date
Msg-id 20060829073811.GA28883@localhost.localdomain
Whole thread Raw
In response to Internal Operations on LIMIT & OFFSET clause  ("Vanitha Jaya" <e.vanitha@gmail.com>)
List 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

pgsql-performance by date:

Previous
From: "Vanitha Jaya"
Date:
Subject: Internal Operations on LIMIT & OFFSET clause
Next
From: Willo van der Merwe
Date:
Subject: PostgreSQL performance issues