Dear All,
Thanks for the suggestion ! I tried to change the work_mem and the query only needs 4.9 sec to display the result !
Torsten, your 2nd option didnt work with this error :
ERROR: syntax error at or near "discount"
LINE 1: ...rang, keluar, case when discount<=...
^
For Mat : what command i can use to show how big the tables in MB ?
Thanks
On Dec 1, 2013, at 8:06 PM, Torsten Förtsch wrote:
> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
