| Friends, i need help. 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 (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 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 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)='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=50 shared_buffers=1024MB wall_buffers=16MB max_prepared_transactions=0 work_mem=50MB maintenance_work_mem=256MB 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 = (tblsup 3274.11 28179.15 3832 84 |--Hash Join Hash Cond: ((tbltransaksi.kodebarang)::text = (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 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 ) WITH (oids = 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. |
