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.
