Date: Sun, 17 Oct 1999 11:39:56 +0200
From: Ryszard Kurek <rychu@sky.pl>
To: pgsql-interfaces@postgresql.org
Subject: [INTERFACES] Table viewer for big sorted tables
Message-ID: <19991017113956.A1028@fnet.pl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0pre2i

On Sat, Oct 16, 1999 at 04:20:30PM -0400, Tom Lane wrote:
> 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;

after some tests....

above statement works great, but this no :
 SELECT ... FROM products WHERE product_name < 'current_product' 
 ORDER BY product_name DESC LIMIT 1;
:(

btw. I found that 1st fetch from:
declare c1 cursor for select * from table where id > '' order by id
is extra-fast, but from:

declare c2 cursor for select * from order by id
is extra-slow (index isn't used)

In this case (no prev index from select), 
using good prepared cursor is better, BUT
'move forward all in c1' is slowest then 
select max(id) from table, and cursors are insensitive...
(in very big tables)... and listen/notify doesn't work inside transaction... :(

Now I'm thinking about use of cursors and use a forked child 
to listen on update table trigger-notify (on another connection).
If notify was consumed - send a parent process a signal to inform about
changes in table -> this is time to reopen a cursor ...

What You think about this ???


ps. sorry for my eanglish :)


-- 
pozdrowienia, Ryszard Kurek                                   UIN: 1741033
mailto:rychu@sky.pl  * sms: 501128171@sms.centertel.pl * www.fnet.pl/rychu