Thread: Need more speed for this query :)

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

On Sun, Jun 06, 2004 at 15:36:25 -0700,
  Jerry <jerry.levan@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.