From: Hengky Liwandouw <hengkyliwandouw@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Speed up the query
Date: Sun, 1 Dec 2013 14:21:05 +0800
Message-ID: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hengkyliwandouw@gmail.com
Subject: Re: Speed up the query
In-Reply-To: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox