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