Re: Speed up the query - Mailing list pgsql-performance

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=767225loops=1)" 
"                Filter: ((jualid IS NOT NULL) OR ((returjualid IS NOT NULL) AND (date_part('year'::text,
(tanggal)::timestampwithout 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=85034loops=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.298rows=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=23630loops=1)" 
"              ->  Sort  (cost=221247.80..223164.10 rows=766519 width=376) (actual time=50731.687..54455.528
rows=737662loops=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=737662loops=1)" 
"Total runtime: 56787.136 ms"


Hope you can help.



On Dec 1, 2013, at 4:35 PM, Andreas Kretschmer wrote:

> Hengky Liwandouw <hengkyliwandouw@gmail.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)
THENqry1.keluar ELSE 0::numeric END)), (sum(CASE WHEN (qry1.bulan = 2::double precision) THEN qry1.keluar ELSE
0::numericEND)), (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::doubleprecision)" 
>> "                      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
ELSE0::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::numericEND), 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@gmail.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@postgresql.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance