Maybe a strange question, but: "How long may a cursor live?"

Hello everybody...

I'm working on a web based application (using gnuJSP / JDBC)
that needs to do queries like

SELECT count(textattrib1), text_cat(textattrib1 || ' '), func1(textattrib2)
FROM table1 WHERE textattrib2 >= 'foo' GROUP BY textattrib2 ORDER BY
textattrib2 ASC LIMIT somelimit;

with table1 holding roughly 80000 rows (probably growing some day),
textattrib2 is indexed of course.

Unfortunately the query above is intolerably slow (up to 30 seconds or
so, 2 would be OK...).

Just declaring a cursor for

SELECT count(textattrib1), text_cat(textattrib1 || ' '), func1(textattrib2)
FROM table1 GROUP BY textattrib2 ORDER BY textattrib2 ASC;

didn't help much: fetching my entries from such a cursor is very
fast indeed, but the *first* access to the cursor is still much
too slow.

So I'm wondering about alternatives. Maybe somebody can give a comment
on these:

1. pooling cursors

I've been playing with the idea of writing a bean that lives
in the JSP application scope (and gets started with apache).
That bean would then declare a pool of cursors for the query
shown above and each client (read: session scope bean...) could
request one for its session. A (very) crude prototype of this is
running right now and yes: it is *very* fast. I'm worried however
about the effects of having a number of cursors/transactions hanging
around for a potentially very long time. Will temporary files eat
my disk space away in a few hours? What about RAM and CPU ressources?
Restarting apache and postgres (and that cursor pool with it) every
night and having a few minutes of downtime would be not a big problem
here... But still I'm feeling quite uncomfortable with the whole affair.

2. splitting up table1 by substr(textattrib2, 1, 2)

This would result in smaller (faster) tables table1_aa, table1_ab,...
BUT: in case table1_fo contains less than somelimit entries
satisfying the query I want to union these entries with those
from table1_fp,... until I reach somelimit. So I have to store
information somewhere on what table comes next in the chain.
Taking into account that entries are not distributed evenly
with respect to substr(textattrib2, 1, 2) and that I don't
want to have too many entries in each table to keep performance
high... I guess I would pretty soon end up implementing trees
(and tree traversals). I must confess that I'm somewhat shying
away from that work ;-) (Could probably be easier if PL/pgSQL would
allow for 'computed' tablenames?)

3. a *good* solution I've not found yet?

Maybe I'm just thinking too complicated???
Any other ideas to this problem?

Thanks in advance...
Christian Fritze

--
"The sky above the port was the color of television,
tuned to a dead channel."
-- W.G. --