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

Re: LIMIT clause and long timings

From: Christoph Della Valle <christoph(dot)dellavalle(at)goetheanum(dot)ch>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrea <andrea(dot)b73(at)email(dot)it>, pgsql-novice(at)postgresql(dot)org
Subject: Re: LIMIT clause and long timings
Date: 2006-03-29 05:51:24
Message-ID: 442A205C.40907@goetheanum.ch (view raw or flat)
Thread:
Lists: pgsql-novice
would it be faster to use
SELECT * FROM banche ORDER BY banca DESC LIMIT 10 OFFSET 0;

the sorting is done anyway, so at least we don't have to scan through
the table to get the last 10 records?
or is sorting ASC less timeconsuming than sorting DESC?

regards, christoph

Tom Lane schrieb:
> Andrea <andrea(dot)b73(at)email(dot)it> writes:
> 
>>Then I have tried:
>>SELECT * FROM banche ORDER BY banca LIMIT 10 OFFSET 34000;
>>[....]
>>(10 rows)
> 
> 
>>Time: 2433,000 ms
> 
> 
>>Why do I get this big timing???
> 
> 
> Because the system has to scan through 34000 rows before it gets to the
> ten you asked for.  OFFSET is not some bit of black magic, it just
> causes the executor to discard the first N rows it's computed rather
> than sending them over to the client.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 
> 

In response to

pgsql-novice by date

Next:From: Daniel T. StaalDate: 2006-03-29 14:17:03
Subject: Re: Non-Unique intems
Previous:From: John DeSoiDate: 2006-03-29 04:51:37
Subject: Re: PHP libpq Version != Actual PGSQL Version

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