From: Samuel J. Sutjiono <ssutjiono@wc-group.com>
To: pgsql-sql@postgresql.org
To: pgsql-general@postgresql.org
Subject: LIMIT and OFFSET
Date: Fri, 1 Mar 2002 11:24:49 -0500
Message-ID: <007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com> (raw)
Hello all,
I am trying to understand how LIMIT and OFFSET work so I will use the best technique in my search stored procedures (function). Here are my codes:
Scenario:
There are 1,000,000 records in the catalog table and the result set is sorted by Price. All the search fields are indexed.
SELECT * from Catalog where ((VendorName ~* 'dvd|gladiator') or
(ProductModelName ~* 'dvd|gladiator') or
(ProductCategory ~* 'dvd|gladiator') or
(ProductDescr ~* 'dvd|gladiator')) and
(ProductMfr ILIKE 'sony')
ORDER BY Price
LIMIT 10 OFFSET 0
Questions:
1. If the search results in 50,000 rows, does the query dump all the rows to memory ?
2. Does the query do the search until it hits the 1,000,000th record and return the first 10 rows ?
3. If I set OFFSET to 10 (LIMIT 10 OFFSET 10), what is the query process ?
4. If I remove the sort, does the query do the same thing ?
I also need advice about the options that I have and if anybody can recommend the best technique in dealing with searches on large table. I really appreciate your help.
Best Regards,
Samuel
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ssutjiono@wc-group.com, pgsql-general@postgresql.org
Subject: Re: LIMIT and OFFSET
In-Reply-To: <007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com>
* 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