Re: Speed up the query

On 01/12/13 10:07, Hengky Liwandouw wrote:
> with qry1 as
> (select tanggal, extract(month from tanggal) as bulan, tblsupplier.id, nama, kodebarang, namabarang, keluar,
>     case when discount<=100 then
>         keluar*(harga -(discount/100*harga))
>     when tbltransaksi.discount>100 then
>         keluar*(harga-discount)
>     end
>     as jumlah
> from tbltransaksi
> join tblproduk on tbltransaksi.kodebarang=tblproduk.produkid
> join tblsupplier on tblproduk.supplierid=tblsupplier.id
> where jualid is not null or returjualid is not null
> and extract(year from tanggal)='2013')
>
> select
>   id, nama, kodebarang, namabarang,
>   sum(case when bulan = 1 then keluar else 0 end) as Jan,
>   sum(case when bulan = 2 then keluar else 0 end) as Feb,
>   sum(case when bulan = 3 then keluar else 0 end) as Maret,
>   sum(case when bulan = 4 then keluar else 0 end) as April,
>   sum(case when bulan = 5 then keluar else 0 end) as Mei,
>   sum(case when bulan = 6 then keluar else 0 end) as Juni,
>   sum(case when bulan = 7 then keluar else 0 end) as Juli,
>   sum(case when bulan = 8 then keluar else 0 end) as Agust,
>   sum(case when bulan = 9 then keluar else 0 end) as Sept,
>   sum(case when bulan = 10 then keluar else 0 end) as Okt,
>   sum(case when bulan = 11 then keluar else 0 end) as Nov,
>   sum(case when bulan = 12 then keluar else 0 end) as Des,
>   sum(coalesce(keluar,0)) as total
> from qry1
> group by id, nama, kodebarang, namabarang
> order by total desc
> limit 1000
>
> this is the explain analyse :
>
> "Limit  (cost=346389.90..346392.40 rows=1000 width=376) (actual time=56765.848..56766.229 rows=1000 loops=1)"
> "  CTE qry1"
> "    ->  Hash Join  (cost=4444.64..62683.91 rows=766519 width=84) (actual time=87.342..1786.851 rows=737662 loops=1)"
> "          Hash Cond: ((tbltransaksi.kodebarang)::text = (tblproduk.produkid)::text)"
> "          ->  Seq Scan on tbltransaksi  (cost=0.00..24704.06 rows=766519 width=29) (actual time=0.010..271.147
rows=767225loops=1)" 
> "                Filter: ((jualid IS NOT NULL) OR ((returjualid IS NOT NULL) AND (date_part('year'::text,
(tanggal)::timestampwithout time zone) = 2013::double precision)))" 
> "                Rows Removed by Filter: 37441"
> "          ->  Hash  (cost=3380.52..3380.52 rows=85130 width=68) (actual time=87.265..87.265 rows=65219 loops=1)"
> "                Buckets: 16384  Batches: 1  Memory Usage: 5855kB"
> "                ->  Hash Join  (cost=21.68..3380.52 rows=85130 width=68) (actual time=0.748..59.469 rows=65219
loops=1)"
> "                      Hash Cond: ((tblproduk.supplierid)::text = (tblsupplier.id)::text)"
> "                      ->  Seq Scan on tblproduk  (cost=0.00..2188.30 rows=85130 width=51) (actual time=0.005..17.184
rows=85034loops=1)" 
> "                      ->  Hash  (cost=14.08..14.08 rows=608 width=26) (actual time=0.730..0.730 rows=609 loops=1)"
> "                            Buckets: 1024  Batches: 1  Memory Usage: 28kB"
> "                            ->  Seq Scan on tblsupplier  (cost=0.00..14.08 rows=608 width=26) (actual
time=0.006..0.298rows=609 loops=1)" 
> "  ->  Sort  (cost=283705.99..283897.62 rows=76652 width=376) (actual time=56765.846..56766.006 rows=1000 loops=1)"
> "        Sort Key: (sum(COALESCE(qry1.keluar, 0::numeric)))"
> "        Sort Method: top-N heapsort  Memory: 280kB"
> "        ->  GroupAggregate  (cost=221247.80..279503.25 rows=76652 width=376) (actual time=50731.735..56739.181
rows=23630loops=1)" 
> "              ->  Sort  (cost=221247.80..223164.10 rows=766519 width=376) (actual time=50731.687..54455.528
rows=737662loops=1)" 
> "                    Sort Key: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang"
> "                    Sort Method: external merge  Disk: 71872kB"
> "                    ->  CTE Scan on qry1  (cost=0.00..15330.38 rows=766519 width=376) (actual time=87.346..2577.066
rows=737662loops=1)" 
> "Total runtime: 56787.136 ms"

I'd try 2 things:

1) set work_mem to ~100Mb. You don't have to do that globally in
postgresql.conf. You can set it for the current session only.

  set work_mem to '100MB';

Then run your query.

2) change the common table expression to a subquery:

select
  id, nama, kodebarang, namabarang,
  sum(case when bulan = 1 then keluar else 0 end) as Jan,
  sum(case when bulan = 2 then keluar else 0 end) as Feb,
  sum(case when bulan = 3 then keluar else 0 end) as Maret,
  sum(case when bulan = 4 then keluar else 0 end) as April,
  sum(case when bulan = 5 then keluar else 0 end) as Mei,
  sum(case when bulan = 6 then keluar else 0 end) as Juni,
  sum(case when bulan = 7 then keluar else 0 end) as Juli,
  sum(case when bulan = 8 then keluar else 0 end) as Agust,
  sum(case when bulan = 9 then keluar else 0 end) as Sept,
  sum(case when bulan = 10 then keluar else 0 end) as Okt,
  sum(case when bulan = 11 then keluar else 0 end) as Nov,
  sum(case when bulan = 12 then keluar else 0 end) as Des,
  sum(coalesce(keluar,0)) as total
from (select tanggal, extract(month from tanggal) as bulan,
             tblsupplier.id, nama, kodebarang, namabarang, keluar,
             case
               when discount<=100 then
                 keluar*(harga -(discount/100*harga))
               when tbltransaksi.discount>100 then
                 keluar*(harga-discount)
             end as jumlah
        from tbltransaksi
        join tblproduk on tbltransaksi.kodebarang=tblproduk.produkid
        join tblsupplier on tblproduk.supplierid=tblsupplier.id
       where jualid is not null or returjualid is not null
         and extract(year from tanggal)='2013') qry1
group by id, nama, kodebarang, namabarang
order by total desc
limit 1000

Selamat berjaya,
Torsten