From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Speed up the query
Date: Sun, 1 Dec 2013 09:35:41 +0100
Message-ID: <20131201083541.GA10012@tux> (raw)
In-Reply-To: <335A179F-B1A5-4634-998F-BA30ACD84209@gmail.com>
References: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com>
<20131201071231.GA4780@tux>
<335A179F-B1A5-4634-998F-BA30ACD84209@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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) 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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: Speed up the query
In-Reply-To: <20131201083541.GA10012@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox