Re: Speed up the query

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°