From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ryszard Kurek <rychu@sky.pl>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Table viewer for big sorted tables
Date: Sat, 16 Oct 1999 16:20:30 -0400
Message-ID: <9459.940105230@sss.pgh.pa.us> (raw)
In-Reply-To: <19991016210215.A4354@fnet.pl>
Ryszard Kurek <rychu@sky.pl> writes:
> The slow version is:
> SELECT min(product_symbol) FROM products WHERE product_name > 'current_product';
Try
SELECT ... FROM products WHERE product_name > 'current_product' ORDER BY product_name LIMIT 1;
You will need to have an index on product_name to make this fast ---
without the index there will be a sort step.
The ideal solution to your problem is probably
SELECT ... FROM products ORDER BY product_name LIMIT 1 OFFSET n;
which avoids issues like what happens when there is more than one row
with the same product_name. However, 6.5.* is not bright enough to
use an index for this kind of query (it only considers an index if
there is a matching WHERE clause). 7.0 will be smarter.
regards, tom lane
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-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, rychu@sky.pl
Subject: Re: [INTERFACES] Table viewer for big sorted tables
In-Reply-To: <9459.940105230@sss.pgh.pa.us>
* 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