Message-ID: <529B265B.5030206@gmx.net>
Date: Sun, 01 Dec 2013 13:06:51 +0100
From: =?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: Hengky Liwandouw <hengkyliwandouw@gmail.com>,
 pgsql-performance@postgresql.org
Subject: Re: Speed up the query
References: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com>
 <20131201071231.GA4780@tux> <335A179F-B1A5-4634-998F-BA30ACD84209@gmail.com>
 <20131201083541.GA10012@tux> <980E5A18-104B-46C8-AB98-A66A391C8272@gmail.com>
In-Reply-To: <980E5A18-104B-46C8-AB98-A66A391C8272@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance