Received: from qmsql.netservers.net (hpmail4.netservers.net [209.196.128.183]) by postgresql.org (Postfix) with SMTP id 20EE7476236 for ; Fri, 1 Mar 2002 11:29:45 -0500 (EST) Received: (qmail 58143 invoked from network); 1 Mar 2002 16:29:32 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail4.netservers.net with SMTP; 1 Mar 2002 16:29:32 -0000 Message-ID: <007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: LIMIT and OFFSET Date: Fri, 1 Mar 2002 11:24:49 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_006F_01C1C113.B2D1FC50" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200203/18 X-Sequence-Number: 21404 This is a multi-part message in MIME format. ------=_NextPart_000_006F_01C1C113.B2D1FC50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 sort= ed by Price. All the search fields are indexed. SELECT * from Catalog where ((VendorName ~* 'dvd|gladiator') or=20 (ProductModelName ~* 'dvd|glad= iator') or=20 (ProductCategory ~* 'dvd|gladi= ator') or=20 (ProductDescr ~* 'dvd|gladiato= r')) and (ProductMfr ILIKE 'sony')=20 ORDER BY Price LIMIT 10 OFFSET 0 Questions: 1. If the search results in 50,000 rows, does the query dump all the rows t= o memory ? 2. Does the query do the search until it hits the 1,000,000th record and r= eturn 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 recomme= nd the best technique in dealing with searches on large table. I really ap= preciate your help. Best Regards, Samuel ------=_NextPart_000_006F_01C1C113.B2D1FC50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello all,
 
I am trying to understand how LIMIT and OF= FSET work=20 so I will use the best technique in my search stored procedures=20 (function).  Here are my codes:
 
Scenario:
There are 1,= 000,000=20 records in the catalog table and the result set is sorted by Price. All the= =20 search fields are indexed.
 
SELECT * from Catalog where ((VendorName ~= *=20 ‘dvd|gladiator’) or=20
            = ;            &n= bsp;            = ;       =20 (ProductModelName ~* ‘dvd|gladiator’) or=20
            = ;            &n= bsp;            = ;       =20 (ProductCategory ~* ‘dvd|gladiator’) or=20
            = ;            &n= bsp;            = ;       =20 (ProductDescr ~* ‘dvd|gladiator’))=20 and
           &n= bsp;            = ;            &n= bsp;       =20 (ProductMfr ILIKE ‘sony’)
ORDER BY Price
LIMIT 10  = OFFSET=20 0
 
Questions:
1. If the s= earch=20 results in 50,000 rows, does the query dump all the rows to memory ?
2. = Does=20 the query do the search until it hits the 1,000,000th  record and retu= rn=20 the first 10 rows ?
3. If I set  OFFSET to 10 (LIMIT 10 OFFSET 10),= what=20 is the query process ?
4. If I remove the sort, does the query do the sa= me=20 thing ?
 
I also need advice about the options that = I have=20 and if anybody can recommend the best technique in dealing with searches on= =20 large table.  I really appreciate your help.
 
Best Regards,
Samuel

------=_NextPart_000_006F_01C1C113.B2D1FC50--