Thank you for the timely comment.. :) The database is Postgres using the ltree extension. I've done all I can in the tuning side for now, I've fooled with various ways of doing the query, but its just a matter of the type of work to be done.
Re^2: Architectural question...
Replies are listed 'Best First'.
But, as you said, you aren't doing the same kind of work in perl ... so it's likely that if you do the work differently in SQL it'll be faster. I'd put money on postgresql vs. perl for calculating a table of numbers from another set of numbers.
Saying that it matters a _lot_ what version of postgresql you are using, so an upgrade to postgresql-8.0 could provide the speedup you need ... and an increase in other parts :).
I am runing postgres 7.4. I don't see much on the Postgres site about performance improvements, except for the somewhat vague statement "This release has a more intelligent buffer replacement strategy, which will make better use of available shared buffers and improve performance.".
The situation (on my development box) is one of *SEVERE* disk thrashing. Just doing a "select count(category),category from data" and I can hear the disk drive sounding like it is going to take off.
While I agree that Postgres can summarize rows faster then I could ever hope to, it lacks the sophisticated cache configurations that are possible with other RDBMS (notably Sybase or Informix, which if I could use either of those here could probably make this problem a non-issue).
That being said, I will give upgrading Postgres a shot, but I am not optimistic as it still relies on the operating system to cache files and thus there is no way to ensure certain tables have a higher caching priority.