pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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