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

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°