Date: Sun, 1 Dec 2013 09:35:41 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Speed up the query
Message-ID: <20131201083541.GA10012@tux>
References: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com>
 <20131201071231.GA4780@tux> <335A179F-B1A5-4634-998F-BA30ACD84209@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <335A179F-B1A5-4634-998F-BA30ACD84209@gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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