Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vn0Qk-0002Wy-Uj for pgsql-performance@arkaria.postgresql.org; Sun, 01 Dec 2013 06:22:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Vn0Qj-00041z-Aa for pgsql-performance@arkaria.postgresql.org; Sun, 01 Dec 2013 06:22:57 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vn0Qg-00041q-O0 for pgsql-performance@postgresql.org; Sun, 01 Dec 2013 06:22:55 +0000 Received: from mail-pb0-x229.google.com ([2607:f8b0:400e:c01::229]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vn0QY-0002ou-2S for pgsql-performance@postgresql.org; Sun, 01 Dec 2013 06:22:53 +0000 Received: by mail-pb0-f41.google.com with SMTP id jt11so16900417pbb.28 for ; Sat, 30 Nov 2013 22:22:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:subject:date:message-id:to:mime-version; bh=r2tMtXXBMr8LWVVQeyz9/nZJuEF/KF++N95HnqIgNBI=; b=rBZbbfU7PgryCUM9Mzf9i24FZzF4eaxgRae2b9/1QQ+NXZpm9J1CZ1BgqugMaaMMW6 UAdsF9sQVju5yPBZ3HOlTgMHenC/BWir11UGw2lQ670nvsWelOk+boyApJ4JYED5b2k4 Q86ynei5E/lgI5mRMhqc4PTGiyTnKpbEhv08mO2Tsa+LWfyWgZ7naS8tv+ZSlMEd2gBL JWeuanKd41DLp6AF/0rBBU0MQFiKbFrZenZXyxc8BDWyON7K4lB6BBjiW2TR3+ZjWYvj V+voJggLKVcqlYnirKSaiuTvocQLZcFiGArVzgesNpsFNHLC97KVZL3XbCOHdH/dLKZG yd/g== X-Received: by 10.68.241.227 with SMTP id wl3mr23974071pbc.111.1385878964182; Sat, 30 Nov 2013 22:22:44 -0800 (PST) Received: from [192.168.1.111] ([110.138.251.110]) by mx.google.com with ESMTPSA id ki1sm113350913pbd.1.2013.11.30.22.22.40 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sat, 30 Nov 2013 22:22:42 -0800 (PST) From: Hengky Liwandouw Content-Type: multipart/alternative; boundary=Apple-Mail-2--450227767 Subject: Speed up the query Date: Sun, 1 Dec 2013 14:21:05 +0800 Message-Id: To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1084) X-Mailer: Apple Mail (2.1084) X-Pg-Spam-Score: -0.1 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail-2--450227767 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Friends, i need help.=20 I have query below that running well so far. it needs only 5.335 second = to get data from 803.583 records. Here is the query : with qry1 as=20 (select tanggal, extract(month from tanggal) as bulan, tblsupplier.id, = nama, kodebarang, namabarang, keluar,=20 case when discount<=3D100 then keluar*(harga -(discount/100*harga)) when tbltransaksi.discount>100 then keluar*(harga-discount) end=20 as jumlah from tbltransaksi=20 join tblproduk on tbltransaksi.kodebarang=3Dtblproduk.produkid join tblsupplier on tblproduk.supplierid=3Dtblsupplier.id where jualid is not null=20 and extract(year from tanggal)=3D'2013') select =20 id, nama, kodebarang, namabarang, sum(case when bulan =3D 1 then keluar else 0 end) as Jan, sum(case when bulan =3D 2 then keluar else 0 end) as Feb, sum(case when bulan =3D 3 then keluar else 0 end) as Maret, sum(case when bulan =3D 4 then keluar else 0 end) as April, sum(case when bulan =3D 5 then keluar else 0 end) as Mei, sum(case when bulan =3D 6 then keluar else 0 end) as Juni, sum(case when bulan =3D 7 then keluar else 0 end) as Juli, sum(case when bulan =3D 8 then keluar else 0 end) as Agust, sum(case when bulan =3D 9 then keluar else 0 end) as Sept, sum(case when bulan =3D 10 then keluar else 0 end) as Okt, sum(case when bulan =3D 11 then keluar else 0 end) as Nov, sum(case when bulan =3D 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 But the problem is : when i change the where clause to : where jualid is not null or returjualid is not null and extract(year from tanggal)=3D'2013') (there is additional or returjualid is not null,) the query needs 56 = second to display the result. 10 times longer. Is there anyway to speed up the query ? My server is Dell PowerEdge = T110II, Intel Xeon E1230 Sandy bridge 3.2GHZ, 4GB memory, 500GB Sata III = HDD running on Ubuntu server 12.04, PostgreSql 9.3 Postgresqlconf : max_connections=3D50 shared_buffers=3D1024MB wall_buffers=3D16MB max_prepared_transactions=3D0 work_mem=3D50MB maintenance_work_mem=3D256MB Analyze result : Operation Operation Info = Start-up Cost Total Cost = Number of Rows Row Width =20= Limit CTE qry1 = 28553.93 28554.89 = 384 376 =20= |--Hash Join Hash Cond: = ((tblproduk.supplierid)::text =3D (tblsup 3274.11 = 28179.15 3832 84 = =20 |--Hash Join Hash Cond: = ((tbltransaksi.kodebarang)::text =3D (tbl 3252.43 = 28008.98 3832 67 = =20 |--Seq Scan on tbltransaks Filter: ((jualid IS NOT NULL) AND = (date_part('year 0.00 24684.70 = 3832 29 = =20 |--Hash null = 2188.30 2188.30 = 85130 51 =20 |--Seq Scan on tblproduk null = 0.00 2188.30 = 85130 51 =20 |--Hash null = 14.08 14.08 = 608 26 =20 |--Seq Scan on tblsupplier null = 0.00 14.08 = 608 26 =20 Sort Sort Key: (sum(COALESCE(qry1.keluar, = 0::numeric))) 374.78 375.74 = 384 376 =20= |--HashAggregate null = 354.46 358.30 = 384 376 =20= |--CTE Scan on qry1 null = 0.00 76.64 = 3832 376 =20= the table transaksi : CREATE TABLE public.tbltransaksi ( id INTEGER NOT NULL, tanggal DATE, kodebarang VARCHAR(20), masuk NUMERIC(10,2) DEFAULT 0, keluar NUMERIC(10,2) DEFAULT 0, satuan VARCHAR(5), keterangan VARCHAR(30), jenis VARCHAR(5), harga NUMERIC(15,2) DEFAULT 0, discount NUMERIC(10,2) DEFAULT 0, jualid INTEGER, beliid INTEGER, mutasiid INTEGER, nobukti VARCHAR(20), customerid VARCHAR(20), modal NUMERIC(15,2) DEFAULT 0, awalid INTEGER, terimabrgid INTEGER, opnameid INTEGER, returjualid INTEGER, returbeliid INTEGER, CONSTRAINT tbltransaksi_pkey PRIMARY KEY(id), CONSTRAINT tbltransaksi_fk FOREIGN KEY (returjualid) REFERENCES public.tblreturjual(id) ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT tbltransaksi_fk1 FOREIGN KEY (jualid) REFERENCES public.tblpenjualan(id) ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT tbltransaksi_fk2 FOREIGN KEY (beliid) REFERENCES public.tblpembelian(id) ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT tbltransaksi_fk3 FOREIGN KEY (mutasiid) REFERENCES public.tblmutasi(id) ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE, CONSTRAINT tbltransaksi_fk4 FOREIGN KEY (returbeliid) REFERENCES public.tblreturbeli(id) ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE )=20 WITH (oids =3D false); CREATE INDEX tbltransaksi_idx ON public.tbltransaksi USING btree (tanggal); CREATE INDEX tbltransaksi_idx1 ON public.tbltransaksi USING btree (kodebarang COLLATE pg_catalog."default"); CREATE INDEX tbltransaksi_idx2 ON public.tbltransaksi USING btree (customerid COLLATE pg_catalog."default"); CREATE INDEX tbltransaksi_idx3 ON public.tbltransaksi USING btree (awalid); CREATE INDEX tbltransaksi_idx4 ON public.tbltransaksi USING btree (jualid); CREATE INDEX tbltransaksi_idx5 ON public.tbltransaksi USING btree (beliid); CREATE INDEX tbltransaksi_idx6 ON public.tbltransaksi USING btree (mutasiid); CREATE INDEX tbltransaksi_idx7 ON public.tbltransaksi USING btree (opnameid); CREATE INDEX tbltransaksi_idx8 ON public.tbltransaksi USING btree (returjualid); CREATE INDEX tbltransaksi_idx9 ON public.tbltransaksi USING btree (returbeliid); Hope i can get answer here. Thank you.= --Apple-Mail-2--450227767 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii case when discount<=3D100 = then
=    keluar*(harga = -(discount/100*harga))
when tbltransaksi.discount>100 = then
= keluar*(harga-discount)
= end 
    as jumlah
from = tbltransaksi 
join tblproduk on = tbltransaksi.kodebarang=3Dtblproduk.produkid
join tblsupplier = on tblproduk.supplierid=3Dtblsupplier.id
where jualid is not = null 
and extract(year from = tanggal)=3D'2013')

select =  
  id, nama, kodebarang, = namabarang,
  sum(case when bulan =3D 1 then keluar else = 0 end) as Jan,
  sum(case when bulan =3D 2 then keluar = else 0 end) as Feb,
  sum(case when bulan =3D 3 then = keluar else 0 end) as Maret,
  sum(case when bulan =3D 4 = then keluar else 0 end) as April,
  sum(case when bulan =3D= 5 then keluar else 0 end) as Mei,
  sum(case when bulan = =3D 6 then keluar else 0 end) as Juni,
  sum(case when = bulan =3D 7 then keluar else 0 end) as Juli,
  sum(case = when bulan =3D 8 then keluar else 0 end) as Agust,
  = sum(case when bulan =3D 9 then keluar else 0 end) as = Sept,
  sum(case when bulan =3D 10 then keluar else 0 = end) as Okt,
  sum(case when bulan =3D 11 then keluar = else 0 end) as Nov,
  sum(case when bulan =3D 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

But the problem is : when i change = the where clause to :

where jualid is not = null or returjualid is not null
and extract(year from = tanggal)=3D'2013')


(there = is additional or returjualid is not null,) the query = needs 56 second to display the result. 10 times longer.
Is = there anyway to speed up the query ? My server is Dell PowerEdge = T110II, Intel Xeon E1230 Sandy bridge 3.2GHZ, 4GB memory, 500GB Sata III = HDD running on Ubuntu server 12.04, PostgreSql = 9.3

Postgresqlconf = :
max_connections=3D50
shared_buffers=3D1024MB
wall_buffers=3D16MB
max_prepared_transactions=3D0
w= ork_mem=3D50MB
maintenance_work_mem=3D256MB

=
Analyze result :

Operation   =                     =  Operation Info               =                     =     Start-up Cost             =        Total Cost           =             Number of Rows     =               Row Width     =                 =   
Limit                 =              CTE qry1     =                     =                   28553.93 =                     =     28554.89               =           384         =                     =  376                   =           
  |--Hash = Join                   =   Hash Cond: ((tblproduk.supplierid)::text =3D (tblsup 3274.11 =                     =      28179.15             =             3832       =                     =   84                   =            
    = |--Hash Join                 =   Hash Cond: ((tbltransaksi.kodebarang)::text =3D (tbl 3252.43 =                     =      28008.98             =             3832       =                     =   67                   =            
    =   |--Seq Scan on tbltransaks   Filter: ((jualid IS NOT NULL) = AND (date_part('year 0.00             =                 24684.70   =                     =   3832                 =             29         =                     =  
      |--Hash           =          null         =                     =                     = 2188.30                   =        2188.30           =                85130   =                     =      51               =               =  
        |--Seq Scan on tblproduk null   =                     =                     =       0.00             =                 2188.30   =                     =    85130               =              51       =                     =    
    |--Hash             =          null         =                     =                     = 14.08                   =          14.08         =                    608 =                     =          26           =                   =  
      |--Seq Scan on tblsupplier null   =                     =                     =       0.00             =                 14.08   =                     =      608               =                26     =                     =      
Sort     =                     =       Sort Key: (sum(COALESCE(qry1.keluar, 0::numeric))) = 374.78                   =         375.74           =                 384   =                     =        376             =               =   
  |--HashAggregate             =   null                 =                     =             354.46       =                     = 358.30                   =         384           =                    376 =                     =         
    = |--CTE Scan on qry1          null     =                     =                     =     0.00               =               76.64     =                     =    3832               =               376     =                     =   =   


t= he table transaksi :

CREATE TABLE = public.tbltransaksi (
  id INTEGER NOT = NULL,
  tanggal DATE,
  kodebarang = VARCHAR(20),
  masuk NUMERIC(10,2) DEFAULT = 0,
  keluar NUMERIC(10,2) DEFAULT 0,
  = satuan VARCHAR(5),
  keterangan = VARCHAR(30),
  jenis VARCHAR(5),
  harga = NUMERIC(15,2) DEFAULT 0,
  discount NUMERIC(10,2) DEFAULT = 0,
  jualid INTEGER,
  beliid = INTEGER,
  mutasiid INTEGER,
  nobukti = VARCHAR(20),
  customerid VARCHAR(20),
  = modal NUMERIC(15,2) DEFAULT 0,
  awalid = INTEGER,
  terimabrgid INTEGER,
  opnameid = INTEGER,
  returjualid INTEGER,
  = returbeliid INTEGER,
  CONSTRAINT tbltransaksi_pkey = PRIMARY KEY(id),
  CONSTRAINT tbltransaksi_fk FOREIGN KEY = (returjualid)
    REFERENCES = public.tblreturjual(id)
    ON DELETE = CASCADE
    ON UPDATE NO ACTION
  =   DEFERRABLE
    INITIALLY = IMMEDIATE,
  CONSTRAINT tbltransaksi_fk1 FOREIGN KEY = (jualid)
    REFERENCES = public.tblpenjualan(id)
    ON DELETE = CASCADE
    ON UPDATE NO ACTION
  =   NOT DEFERRABLE,
  CONSTRAINT tbltransaksi_fk2 = FOREIGN KEY (beliid)
    REFERENCES = public.tblpembelian(id)
    ON DELETE = CASCADE
    ON UPDATE NO ACTION
  =   NOT DEFERRABLE,
  CONSTRAINT tbltransaksi_fk3 = FOREIGN KEY (mutasiid)
    REFERENCES = public.tblmutasi(id)
    ON DELETE = CASCADE
    ON UPDATE NO ACTION
  =   NOT DEFERRABLE,
  CONSTRAINT tbltransaksi_fk4 = FOREIGN KEY (returbeliid)
    REFERENCES = public.tblreturbeli(id)
    ON DELETE = CASCADE
    ON UPDATE NO ACTION
  =   NOT DEFERRABLE
) 
WITH (oids =3D = false);

CREATE INDEX tbltransaksi_idx ON = public.tbltransaksi
  USING btree = (tanggal);

CREATE INDEX tbltransaksi_idx1 ON = public.tbltransaksi
  USING btree (kodebarang COLLATE = pg_catalog."default");

CREATE INDEX = tbltransaksi_idx2 ON public.tbltransaksi
  USING btree = (customerid COLLATE = pg_catalog."default");

CREATE INDEX = tbltransaksi_idx3 ON public.tbltransaksi
  USING btree = (awalid);

CREATE INDEX tbltransaksi_idx4 ON = public.tbltransaksi
  USING btree = (jualid);

CREATE INDEX tbltransaksi_idx5 ON = public.tbltransaksi
  USING btree = (beliid);

CREATE INDEX tbltransaksi_idx6 ON = public.tbltransaksi
  USING btree = (mutasiid);

CREATE INDEX tbltransaksi_idx7 ON = public.tbltransaksi
  USING btree = (opnameid);

CREATE INDEX tbltransaksi_idx8 ON = public.tbltransaksi
  USING btree = (returjualid);

CREATE INDEX tbltransaksi_idx9 = ON public.tbltransaksi
  USING btree = (returbeliid);



Hope i can get answer here. Thank you.= --Apple-Mail-2--450227767--