Re: Speed up the query

Hello,
your problem seems to arises from the sort that id sone to
disk :

" -> 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)"
infact the qry1 is builded in 2.5 seconds but for the sort it neeses
around 50 seconds.
Try to increase work_mem to almost 100 MB and see if the sort will done in
memory.

it's better you use the explain (analyze,buffers) so we could see the
number of buffers hitted in shared memory.

Could you post how much big in Mb are this tables ?

Mat

2013/12/1 Hengky Liwandouw <hengkyliwandouw(at)gmail(dot)com>

> 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(at)gmail(dot)com> 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(at)gmail(dot)com> 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(at)postgresql(dot)org)
> >>> 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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>