pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
does LIMIT save time?
3+ messages / 2 participants
[nested] [flat]

* does LIMIT save time?
@ 1999-08-19 13:17 Dirk Lutzebaeck <lutzeb@aeccom.com>
  1999-08-19 21:24 ` Re: [SQL] does LIMIT save time? Jan Wieck <wieck@debis.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Dirk Lutzebaeck @ 1999-08-19 13:17 UTC (permalink / raw)
  To: pgsql-sql


Hi,

is using SELECT LIMIT faster than using SELECT without LIMIT on
the same query? If yes, in what way? What is if I use ORDER in the
SELECT clause?

Is it possible to get the *total* number of rows selected (ie. doing
the query virtually without LIMIT) when using a LIMIT clause?

Thanks for help,

Dirk



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] does LIMIT save time?
  1999-08-19 13:17 does LIMIT save time? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 1999-08-19 21:24 ` Jan Wieck <wieck@debis.com>
  1999-08-20 08:13   ` Re: [SQL] does LIMIT save time? Dirk Lutzebaeck <lutzeb@aeccom.com>
  0 siblings, 1 reply; 3+ messages in thread

From: wieck@debis.com @ 1999-08-19 21:24 UTC (permalink / raw)
  To: lutzeb@aeccom.com; +Cc: pgsql-sql

>
>
> Hi,
>
> is using SELECT LIMIT faster than using SELECT without LIMIT on
> the same query? If yes, in what way? What is if I use ORDER in the
> SELECT clause?

    First  of  all,  LIMIT reduces the amount of data sent to the
    client application.  Thus  it  reduces  network  traffic  (or
    interprocess communication if run on the same system), and it
    saves wasted memory in the client to buffer received  results
    that aren't wanted.

    For  the  backend processing time it depends on the execution
    plan generated by the optimizer if  much  or  less.   If  the
    query  plan tells the executor that the result must be sorted
    or grouped,  then  the  entire  result  set  must  first  get
    collected and sorted before the correct portion of the result
    set could be returned. Such a sort or group step is caused by
    the  ORDER  or  GROUP  clauses.  The  optimizer  has  limited
    capability to realize if the index he has choosen for a  scan
    will  already  present  the data in the wanted order and then
    suppress the sorting.

    So in general, LIMIT itself could never hurt.  ORDER can  but
    someone  who  cares  for  speed  is normally willing to setup
    appropriate multi key indices to prevent sort steps.

>
> Is it possible to get the *total* number of rows selected (ie. doing
> the query virtually without LIMIT) when using a LIMIT clause?

    Absolutely not. In  the  optimal  case  (no  sort  step)  the
    executor  aborts  the  table scans when reaching the limit. A
    well placed LIMIT can dramatically reduce disk IO.  Now  look
    at  the  main road and count the red cars passing by over the
    day. After 10 minutes you stop - do you know  how  many  cars
    will have passed at midnight? The executor is exactly in that
    situation - he stop's scanning so he can't know.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] does LIMIT save time?
  1999-08-19 13:17 does LIMIT save time? Dirk Lutzebaeck <lutzeb@aeccom.com>
  1999-08-19 21:24 ` Re: [SQL] does LIMIT save time? Jan Wieck <wieck@debis.com>
@ 1999-08-20 08:13   ` Dirk Lutzebaeck <lutzeb@aeccom.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Dirk Lutzebaeck @ 1999-08-20 08:13 UTC (permalink / raw)
  To: jwieck@debis.com; +Cc: pgsql-sql

Jan Wieck writes:

 >     So in general, LIMIT itself could never hurt.  ORDER can  but
 >     someone  who  cares  for  speed  is normally willing to setup
 >     appropriate multi key indices to prevent sort steps.

Just to know, how can I prevent sorting by using multi key indices?


Thanks for your elaborte answer,

Dirk



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-08-20 08:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-19 13:17 does LIMIT save time? Dirk Lutzebaeck <lutzeb@aeccom.com>
1999-08-19 21:24 ` Jan Wieck <wieck@debis.com>
1999-08-20 08:13   ` Dirk Lutzebaeck <lutzeb@aeccom.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