[thelist] Slow SQL query

[Bill Moseley]

On Wed, Jan 03, 2007 at 07:13:13PM -0800, Bill Moseley wrote:
> I've got two views to do this.  The first one is faster, but
> gives me a NULL result for previous_balance.

Nothing like hitting "send" to jog the memory:, COALESCE():

CREATE VIEW invoice_totals AS

    SELECT
        i.id AS id,
        i.cart AS cart,
        i.invoice_group AS invoice_group,
        i.comment AS comment,
        i.invoice_time AS invoice_time,
        sum(t.amount) AS charges,

        COALESCE((
            SELECT
                sum(tt.amount)
            FROM
                cart_transaction tt
                JOIN invoice ii ON tt.invoice = ii.id
            WHERE
                tt.cart = i.cart
                AND ii.invoice_time < i.invoice_time
        ),0) AS prev_balance


    FROM
        invoice i
        JOIN cart_transaction t ON t.invoice = i.id

    GROUP BY
        1,2, 3,4,5, i.id

    ORDER BY
        i.id, i.invoice_time;


Then for speed, adding an index on cart_transaction.cart made a huge
difference.  Wish I could read the query plans better and see where to
add my indexes.

ws2=> EXPLAIN ANALYZE select * from current_invoices ;
                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=9254.71..11739.90 rows=1399 width=135) (actual time=210.793..231.192 rows=1278 loops=1)
   Merge Cond: ("outer".id = "inner".cart)
   ->  Index Scan using cart_pkey on cart c  (cost=0.00..2363.03 rows=39071 width=23) (actual time=0.194..116.115 rows=39072 loops=1)
   ->  Sort  (cost=9254.71..9258.21 rows=1399 width=116) (actual time=73.801..74.441 rows=1278 loops=1)
         Sort Key: t.cart
         ->  Sort  (cost=9164.12..9167.62 rows=1399 width=61) (actual time=69.406..70.041 rows=1278 loops=1)
               Sort Key: i.id, i.invoice_time
               ->  HashAggregate  (cost=118.62..9091.02 rows=1399 width=61) (actual time=17.208..62.607 rows=1278 loops=1)
                     ->  Hash Join  (cost=26.65..97.64 rows=1399 width=61) (actual time=4.904..11.761 rows=1425 loops=1)
                           Hash Cond: ("outer".invoice = "inner".id)
                           ->  Seq Scan on cart_transaction t  (cost=0.00..33.80 rows=1580 width=13) (actual time=0.008..2.165 rows=1580 loops=1)
                           ->  Hash  (cost=23.12..23.12 rows=1412 width=52) (actual time=4.816..4.816 rows=1278 loops=1)
                                 ->  Seq Scan on invoice i  (cost=0.00..23.12 rows=1412 width=52) (actual time=0.006..2.433 rows=1278 loops=1)
                     SubPlan
                       ->  Aggregate  (cost=6.39..6.40 rows=1 width=9) (actual time=0.028..0.028 rows=1 loops=1278)
                             ->  Nested Loop  (cost=0.00..6.39 rows=1 width=9) (actual time=0.023..0.026 rows=0 loops=1278)
                                   ->  Index Scan using cart_transaction_cart on cart_transaction tt  (cost=0.00..3.36 rows=1 width=13) (actual time=0.008..0.010 rows=1 loops=1278)
                                         Index Cond: (cart = $0)
                                   ->  Index Scan using invoice_pkey on invoice ii  (cost=0.00..3.02 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1775)
                                         Index Cond: ("outer".invoice = ii.id)
                                         Filter: (invoice_time < $1)
 Total runtime: 233.186 ms
(22 rows)


> ws2=> EXPLAIN ANALYZE select * from invoice_totals2;
>                                                                       QUERY PLAN                                                                      
> ------------------------------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=59400.97..59404.50 rows=1412 width=61) (actual time=2286.999..2287.592 rows=1278 loops=1)
>    Sort Key: i.id, i.invoice_time
>    ->  HashAggregate  (cost=120.52..59327.09 rows=1412 width=61) (actual time=18.646..2279.966 rows=1278 loops=1)
>          ->  Hash Join  (cost=26.65..98.35 rows=1478 width=61) (actual time=4.822..11.834 rows=1425 loops=1)
>                Hash Cond: ("outer".invoice = "inner".id)
>                ->  Seq Scan on cart_transaction t  (cost=0.00..34.70 rows=1670 width=13) (actual time=0.015..2.339 rows=1580 loops=1)
>                ->  Hash  (cost=23.12..23.12 rows=1412 width=52) (actual time=4.738..4.738 rows=1278 loops=1)
>                      ->  Seq Scan on invoice i  (cost=0.00..23.12 rows=1412 width=52) (actual time=0.007..2.279 rows=1278 loops=1)
>          SubPlan
>            ->  Aggregate  (cost=41.91..41.92 rows=1 width=9) (actual time=1.762..1.762 rows=1 loops=1278)
>                  ->  Nested Loop  (cost=0.00..41.90 rows=1 width=9) (actual time=1.451..1.758 rows=0 loops=1278)
>                        ->  Seq Scan on cart_transaction tt  (cost=0.00..38.88 rows=1 width=13) (actual time=0.811..1.735 rows=1 loops=1278)
>                              Filter: (cart = $0)
>                        ->  Index Scan using invoice_pkey on invoice ii  (cost=0.00..3.02 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1775)
>                              Index Cond: ("outer".invoice = ii.id)
>                              Filter: (invoice_time < $1)
>  Total runtime: 2288.823 ms
> (17 rows)

-- 
Bill Moseley
moseley at hank.org