Re: Need more speed for this query :)

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