Ok, i just recreate the index :
CREATE INDEX tbltransaksi_idx10
ON tbltransaksi
USING btree
(date_part('year'::text, tanggal))
WHERE jualid IS NOT NULL OR returjualid IS NOT NULL;
(PGAdminIII always convert extract(year from tanggal) to date_part('year'::text,tanggal))
This is the product table
CREATE TABLE public.tblproduk (
produkid VARCHAR(20) NOT NULL,
namabarang VARCHAR(50),
hargajual NUMERIC(15,2) DEFAULT 0,
subkategoriid VARCHAR(10),
createby VARCHAR(10),
kodepromo VARCHAR(10),
satuan VARCHAR(5),
foto BYTEA,
pajak BOOLEAN,
listingfee BOOLEAN,
supplierid VARCHAR(20),
modifyby VARCHAR(10),
qtygrosir INTEGER DEFAULT 0,
hargagrosir NUMERIC(15,2) DEFAULT 0,
diskonjual NUMERIC(5,2) DEFAULT 0,
modal NUMERIC(15,2) DEFAULT 0,
CONSTRAINT tblproduk_pkey PRIMARY KEY(produkid)
)
WITH (oids = false);
CREATE INDEX tblproduk_idx ON public.tblproduk
USING btree (namabarang COLLATE pg_catalog."default");
CREATE INDEX tblproduk_idx1 ON public.tblproduk
USING btree (supplierid COLLATE pg_catalog."default");
CREATE INDEX tblproduk_idx2 ON public.tblproduk
USING btree (subkategoriid COLLATE pg_catalog."default");
Supplier table :
CREATE TABLE public.tblsupplier (
id VARCHAR(20) NOT NULL,
nama VARCHAR(50),
alamat VARCHAR(50),
telepon VARCHAR(50),
kontak VARCHAR(50),
email VARCHAR(50),
kota VARCHAR(50),
hp VARCHAR(50),
createby VARCHAR(10),
modifyby VARCHAR(10),
CONSTRAINT tblsupplier_pkey PRIMARY KEY(id)
)
WITH (oids = false);
CREATE INDEX tblsupplier_idx ON public.tblsupplier
USING btree (nama COLLATE pg_catalog."default");
CREATE INDEX tblsupplier_idx1 ON public.tblsupplier
USING btree (kota COLLATE pg_catalog."default");
Transaksi table :
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_idx10 ON public.tbltransaksi
USING btree ((date_part('year'::text, tanggal)))
WHERE ((jualid IS NOT NULL) OR (returjualid IS NOT NULL));
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);
the query that run slow:
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 or returjualid 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
this is the explain analyse :
"Limit (cost=346389.90..346392.40 rows=1000 width=376) (actual time=56765.848..56766.229 rows=1000 loops=1)"
" CTE qry1"
" -> Hash Join (cost=4444.64..62683.91 rows=766519 width=84) (actual time=87.342..1786.851 rows=737662 loops=1)"
" Hash Cond: ((tbltransaksi.kodebarang)::text = (tblproduk.produkid)::text)"
" -> Seq Scan on tbltransaksi (cost=0.00..24704.06 rows=766519 width=29) (actual time=0.010..271.147 rows=767225 loops=1)"
" Filter: ((jualid IS NOT NULL) OR ((returjualid IS NOT NULL) AND (date_part('year'::text, (tanggal)::timestamp without time zone) = 2013::double precision)))"
" Rows Removed by Filter: 37441"
" -> Hash (cost=3380.52..3380.52 rows=85130 width=68) (actual time=87.265..87.265 rows=65219 loops=1)"
" Buckets: 16384 Batches: 1 Memory Usage: 5855kB"
" -> Hash Join (cost=21.68..3380.52 rows=85130 width=68) (actual time=0.748..59.469 rows=65219 loops=1)"
" Hash Cond: ((tblproduk.supplierid)::text = (tblsupplier.id)::text)"
" -> Seq Scan on tblproduk (cost=0.00..2188.30 rows=85130 width=51) (actual time=0.005..17.184 rows=85034 loops=1)"
" -> Hash (cost=14.08..14.08 rows=608 width=26) (actual time=0.730..0.730 rows=609 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 28kB"
" -> Seq Scan on tblsupplier (cost=0.00..14.08 rows=608 width=26) (actual time=0.006..0.298 rows=609 loops=1)"
" -> Sort (cost=283705.99..283897.62 rows=76652 width=376) (actual time=56765.846..56766.006 rows=1000 loops=1)"
" Sort Key: (sum(COALESCE(qry1.keluar, 0::numeric)))"
" Sort Method: top-N heapsort Memory: 280kB"
" -> GroupAggregate (cost=221247.80..279503.25 rows=76652 width=376) (actual time=50731.735..56739.181 rows=23630 loops=1)"
" -> Sort (cost=221247.80..223164.10 rows=766519 width=376) (actual time=50731.687..54455.528 rows=737662 loops=1)"
" Sort Key: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang"
" Sort Method: external merge Disk: 71872kB"
" -> CTE Scan on qry1 (cost=0.00..15330.38 rows=766519 width=376) (actual time=87.346..2577.066 rows=737662 loops=1)"
"Total runtime: 56787.136 ms"
Hope you can help.
On Dec 1, 2013, at 4:35 PM, Andreas Kretschmer wrote:
> Hengky Liwandouw <hengkyliwandouw@xxxxxxxxx> wrote:
>
>> Thanks Adreas,
>>
>> Already try your suggestion but it not help. This is the index i created :
>>
>> CREATE INDEX tbltransaksi_idx10 ON public.tbltransaksi
>> USING btree ((date_part('year'::text, tanggal)));
>
> I wrote:
>
>> create index xxx on public.tbltransaksi((extract(year from
>> tanggal))) where jualid is not null or returjualid is not null;
>
> 2 lines, with the where-condition ;-)
>
> Your explain isn't a explain ANALYSE, and it's not for the 2nd query
> (with condition on returjualid)
>
> Do you have propper indexes on tblsupplier.id and tblproduk.produkid?
>
> I see seq-scans there...
>
>
>>
>> Speed is the same. Here is the analyse result :
>>
>> "Limit (cost=11821.17..11822.13 rows=384 width=376)"
>> " Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, (sum(CASE WHEN (qry1.bulan = 1::double precision) THEN qry1.keluar ELSE 0::numeric END)), (sum(CASE WHEN (qry1.bulan = 2::double precision) THEN qry1.keluar ELSE 0::numeric END)), (sum(CASE WH (...)"
>> " CTE qry1"
>> " -> Hash Join (cost=3353.66..11446.48 rows=3831 width=84)"
>> " Output: tbltransaksi.tanggal, date_part('month'::text, (tbltransaksi.tanggal)::timestamp without time zone), tblsupplier.id, tblsupplier.nama, tbltransaksi.kodebarang, tblproduk.namabarang, tbltransaksi.keluar, CASE WHEN (tbltransaksi.discount <= (...)"
>> " Hash Cond: ((tblproduk.supplierid)::text = (tblsupplier.id)::text)"
>> " -> Hash Join (cost=3331.98..11276.35 rows=3831 width=67)"
>> " Output: tbltransaksi.tanggal, tbltransaksi.kodebarang, tbltransaksi.keluar, tbltransaksi.discount, tbltransaksi.harga, tblproduk.namabarang, tblproduk.supplierid"
>> " Hash Cond: ((tbltransaksi.kodebarang)::text = (tblproduk.produkid)::text)"
>> " -> Bitmap Heap Scan on public.tbltransaksi (cost=79.55..7952.09 rows=3831 width=29)"
>> " Output: tbltransaksi.id, tbltransaksi.tanggal, tbltransaksi.kodebarang, tbltransaksi.masuk, tbltransaksi.keluar, tbltransaksi.satuan, tbltransaksi.keterangan, tbltransaksi.jenis, tbltransaksi.harga, tbltransaksi.discount, tbltransaksi (...)"
>> " Recheck Cond: (date_part('year'::text, (tbltransaksi.tanggal)::timestamp without time zone) = 2013::double precision)"
>> " Filter: (tbltransaksi.jualid IS NOT NULL)"
>> " -> Bitmap Index Scan on tbltransaksi_idx10 (cost=0.00..78.59 rows=4022 width=0)"
>> " Index Cond: (date_part('year'::text, (tbltransaksi.tanggal)::timestamp without time zone) = 2013::double precision)"
>> " -> Hash (cost=2188.30..2188.30 rows=85130 width=51)"
>> " Output: tblproduk.namabarang, tblproduk.produkid, tblproduk.supplierid"
>> " -> Seq Scan on public.tblproduk (cost=0.00..2188.30 rows=85130 width=51)"
>> " Output: tblproduk.namabarang, tblproduk.produkid, tblproduk.supplierid"
>> " -> Hash (cost=14.08..14.08 rows=608 width=26)"
>> " Output: tblsupplier.id, tblsupplier.nama"
>> " -> Seq Scan on public.tblsupplier (cost=0.00..14.08 rows=608 width=26)"
>> " Output: tblsupplier.id, tblsupplier.nama"
>> " -> Sort (cost=374.69..375.65 rows=384 width=376)"
>> " Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, (sum(CASE WHEN (qry1.bulan = 1::double precision) THEN qry1.keluar ELSE 0::numeric END)), (sum(CASE WHEN (qry1.bulan = 2::double precision) THEN qry1.keluar ELSE 0::numeric END)), (sum(C (...)"
>> " Sort Key: (sum(COALESCE(qry1.keluar, 0::numeric)))"
>> " -> HashAggregate (cost=354.37..358.21 rows=384 width=376)"
>> " Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, sum(CASE WHEN (qry1.bulan = 1::double precision) THEN qry1.keluar ELSE 0::numeric END), sum(CASE WHEN (qry1.bulan = 2::double precision) THEN qry1.keluar ELSE 0::numeric END), sum( (...)"
>> " -> CTE Scan on qry1 (cost=0.00..76.62 rows=3831 width=376)"
>> " Output: qry1.tanggal, qry1.bulan, qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, qry1.keluar, qry1.jumlah"
>>
>> On Dec 1, 2013, at 3:12 PM, Andreas Kretschmer wrote:
>>
>>> Hengky Liwandouw <hengkyliwandouw@xxxxxxxxx> wrote:
>>>>
>>>> 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')
>>>
>>> Try to create this index:
>>>
>>> create index xxx on public.tbltransaksi((extract(year from tanggal)))
>>> where jualid is not null or returjualid is not null;
>>>
>>> an run the query again, and if this not helps show us explain analyse,
>>> you can use explain.depesz.com to provide us the plan.
>>>
>>>
>>> Andreas
>>> --
>>> Really, I'm not out to destroy Microsoft. That will just be a completely
>>> unintentional side effect. (Linus Torvalds)
>>> "If I was god, I would recompile penguin with --enable-fly." (unknown)
>>> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
