Re: Speed up the query

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=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.184rows=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.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