Need more speed for this query :)

[Jerry]

Hi,

I found an interesting article on running totals at
http://www.databasejournal.com/featu...le.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

[Bruno Wolff III]

On Sun, Jun 06, 2004 at 15:36:25 -0700,
Jerry <je*********@eku.edu> wrote:

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

If you don't have an index on (category, oid) the check for new categories
is going to result in O(n^2) steps. And then a sort step will probably be
tacked on at the end.

The summations might not be very efficient either. You might end up with
a table scan for each category if there aren't too many of them.

You might get a better plan doing a normal group by summation for
the subtotals union with a summation over the full table for the
grand total. It is possible that this could be done by one index scan
(assuming an index on category) over the table generating the category
subtotals in order and then merge sorting that with the result of a table
scan to get the grand total. I don't know if the optimizer will do that well
though.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Bruno Wolff III]

On Sun, Jun 06, 2004 at 15:36:25 -0700,
Jerry <je*********@eku.edu> wrote:

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

If you don't have an index on (category, oid) the check for new categories
is going to result in O(n^2) steps. And then a sort step will probably be
tacked on at the end.

The summations might not be very efficient either. You might end up with
a table scan for each category if there aren't too many of them.

You might get a better plan doing a normal group by summation for
the subtotals union with a summation over the full table for the
grand total. It is possible that this could be done by one index scan
(assuming an index on category) over the table generating the category
subtotals in order and then merge sorting that with the result of a table
scan to get the grand total. I don't know if the optimizer will do that well
though.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly