From: | jerry(dot)levan(at)eku(dot)edu (Jerry) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Need more speed for this query :) |
Date: | 2004-06-06 22:36:25 |
Message-ID: | 46850fd1.0406061436.55a9047@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I found an interesting article on running totals at
http://www.databasejournal.com/features/mssql/article.php/3112381.
I have converted one to postgresql for my banking account:
-- Subtotals on categories and a GrandTotal
select *,
case when a.oid= (select oid from nchecks where a.category
=category order by category desc, oid desc limit 1)
then (select sum(amount)::text from nchecks where oid <= a.oid
and a.category=category)
else ' '
end as SubTotal,
case when a.oid = (select oid from nchecks order by category
desc, oid desc limit 1)
then (select sum(amount) from nchecks)::text
else ' '
end as GrandTotal
from nchecks a
order by category ,oid
The account has a 'category' for each transaction and of course an
'amount' for
the transaction ( and some other fields...)
The table does not have any fields defined as keys ( I am using oids
with there being
no chance for overflow...).
With only ~3300 rows the rascal takes its time...
explain begins with...
Sort (cost=626576.75..626584.96 rows=3283 width=181).....
Is there any way to get this puppy running a bit faster?
Jerry
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-06-06 22:54:58 | Re: Dropping schemas and "illegal seek" |
Previous Message | Marc G. Fournier | 2004-06-06 22:33:56 | Re: News outage? |