pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

Message-ID: <007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com>
Permalink:  ../007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com/
Also on:    postgresql.org/message-id/007201c1c13d$9c4b7040$110a010a@headquarters.wcgroup.com

 · 

reply

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