pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: wieck@debis.com
To: lutzeb@aeccom.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] does LIMIT save time?
Date: Thu, 19 Aug 1999 23:24:09 +0200 (MET DST)
Message-ID: <m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <14268.1018.192099.531387@blanc.aeccom.com>

>
>
> 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) #





view thread (3+ messages)  latest in thread

Message-ID: <m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de>
Permalink:  ../m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de/
Also on:    postgresql.org/message-id/m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: wieck@debis.com, lutzeb@aeccom.com
  Subject: Re: [SQL] does LIMIT save time?
  In-Reply-To: <m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de>
  References: <14268.1018.192099.531387@blanc.aeccom.com> <m11HZfN-0003kLC@orion.SAPserv.Hamburg.dsh.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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