Skip site navigation (1) Skip section navigation (2)

[INTERFACES] Table viewer for big sorted tables

From: Ryszard Kurek <rychu(at)sky(dot)pl>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: [INTERFACES] Table viewer for big sorted tables
Date: 1999-10-17 09:39:56
Message-ID: 19991017113956.A1028@fnet.pl (view raw or flat)
Thread:
Lists: pgsql-interfaces
On Sat, Oct 16, 1999 at 04:20:30PM -0400, Tom Lane wrote:
> Ryszard Kurek <rychu(at)sky(dot)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(at)sky(dot)pl  * sms: 501128171(at)sms(dot)centertel(dot)pl * www.fnet.pl/rychu

Responses

pgsql-interfaces by date

Next:From: Peter SchaeferDate: 1999-10-17 13:38:23
Subject: Re:
Previous:From: Ryszard KurekDate: 1999-10-16 21:02:50
Subject: Re: [INTERFACES] Table viewer for big sorted tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group