Maybe a strange question, but: "How long may a cursor live?" - Mailing list pgsql-general

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. --