pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTable viewer for big sorted tables
5+ messages / 3 participants
[nested] [flat]
* Table viewer for big sorted tables
@ 1999-10-16 19:02 Ryszard Kurek <rychu@sky.pl>
0 siblings, 1 reply; 5+ messages in thread
From: Ryszard Kurek @ 1999-10-16 19:02 UTC (permalink / raw)
To: pgsql-interfaces
Hi all!
I want to write a PostgreSQL based program in C++ on Linux system,
but I have a problem:
For example, user must fill up the form.
One of the fields is product_symbol that is a key in another table (indexed).
I want user to select the correct product_symbol from list, that
show product_symbol and corresponding fields from products table
in sorted way.
In other words, I want to write table viewer thet allow user to walk on
entire table using cursor keys, Pgup, PgDown and Home/End.
I first thought, that scrollable cursor would be good. I was wrong,
because first fetch from cursor took long time if table
had big number of rows. Cursors are also insensitive :(
and re-opening would take long time again...
I'm thinking about sequentially reads from table with
select statement with where clause.
BUT I don't know how to fast obtain the next (in order) value of product_symbol
(next to current value)
The slow version is:
SELECT min(product_symbol) FROM products WHERE product_name > 'current_product';
This take about 1 second in table with 100 000 records (2 columns only).
If I want to show 20 rows at the same time on screen - this take 20 seconds :(
Any suggestions ???
TIA
--
Ryszard Kurek
rychu@sky.pl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Table viewer for big sorted tables
@ 1999-10-16 20:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ryszard Kurek <rychu@sky.pl>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 1999-10-16 20:20 UTC (permalink / raw)
To: Ryszard Kurek <rychu@sky.pl>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Table viewer for big sorted tables
@ 1999-10-16 21:02 Ryszard Kurek <rychu@sky.pl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Ryszard Kurek @ 1999-10-16 21:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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;
I've tried several combination but not this particual one...
IT WORKing now !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
thank you very much, you saved my life :)
--
pozdrowienia, Ryszard Kurek UIN: 1741033
mailto:rychu@sky.pl * sms: 501128171@sms.centertel.pl * www.fnet.pl/rychu
^ permalink raw reply [nested|flat] 5+ messages in thread
* [INTERFACES] Table viewer for big sorted tables
@ 1999-10-17 09:39 Ryszard Kurek <rychu@sky.pl>
0 siblings, 1 reply; 5+ messages in thread
From: Ryszard Kurek @ 1999-10-17 09:39 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [INTERFACES] Table viewer for big sorted tables
@ 1999-10-18 08:29 Hiroshi Inoue <Inoue@tpf.co.jp>
parent: Ryszard Kurek <rychu@sky.pl>
0 siblings, 0 replies; 5+ messages in thread
From: Hiroshi Inoue @ 1999-10-18 08:29 UTC (permalink / raw)
To: Ryszard Kurek <rychu@sky.pl>; +Cc: pgsql-interfaces
> -----Original Message-----
> From: owner-pgsql-interfaces@postgreSQL.org
> [mailto:owner-pgsql-interfaces@postgreSQL.org]On Behalf Of Ryszard Kurek
> Sent: Sunday, October 17, 1999 6:40 PM
> To: pgsql-interfaces@postgreSQL.org
> Subject: [INTERFACES] Table viewer for big sorted tables
>
>
> 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;
> :(
>
This would also work in 7.0.
You could try it in the current snapshot.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-10-18 08:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-16 19:02 Table viewer for big sorted tables Ryszard Kurek <rychu@sky.pl>
1999-10-16 20:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-16 21:02 ` Ryszard Kurek <rychu@sky.pl>
1999-10-17 09:39 [INTERFACES] Table viewer for big sorted tables Ryszard Kurek <rychu@sky.pl>
1999-10-18 08:29 ` Hiroshi Inoue <Inoue@tpf.co.jp>
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