Thread: LIMIT and OFFSET
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.
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
(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 ?
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