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=767225 loops=1)"
> " Filter: ((jualid IS NOT NULL) OR ((returjualid IS NOT NULL) AND (date_part('year'::text, (tanggal)::timestamp without 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=85034 loops=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.298 rows=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=23630 loops=1)"
> " -> Sort (cost=221247.80..223164.10 rows=766519 width=376) (actual time=50731.687..54455.528 rows=737662 loops=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=737662 loops=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