Speed up the query - Mailing list pgsql-performance
| From | Hengky Liwandouw |
|---|---|
| Subject | Speed up the query |
| Date | |
| Msg-id | B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com Whole thread Raw |
| Responses |
Re: Speed up the query
Re: Speed up the query |
| List | pgsql-performance |
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.
pgsql-performance by date: