Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50849=pgsql-performance-ar@postgresql.org>)
 id 1Vn2Ru-00075s-SL
 for pgsql-performance@arkaria.postgresql.org; Sun, 01 Dec 2013 08:32:19 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50849=pgsql-performance-ar@postgresql.org>)
 id 1Vn2Ru-0003PH-0H
 for pgsql-performance@arkaria.postgresql.org; Sun, 01 Dec 2013 08:32:18 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <hengkyliwandouw@gmail.com>) id 1Vn2Rs-0003PA-KP
 for pgsql-performance@postgresql.org; Sun, 01 Dec 2013 08:32:16 +0000
Received: from mail-pb0-x236.google.com ([2607:f8b0:400e:c01::236])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <hengkyliwandouw@gmail.com>) id 1Vn2Rp-000587-5r
 for pgsql-performance@postgresql.org; Sun, 01 Dec 2013 08:32:15 +0000
Received: by mail-pb0-f54.google.com with SMTP id un15so16809905pbc.41
 for <pgsql-performance@postgresql.org>; Sun, 01 Dec 2013 00:32:12 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=subject:mime-version:content-type:from:in-reply-to:date:cc
 :content-transfer-encoding:message-id:references:to;
 bh=Azxf4o2fPs/URNkzH5LAEumD76HLjAAKhl6PFUKW1l0=;
 b=qaeGtH4RSljqYPUHr9WSEXBP9qSBHGpvcPdJCB21BM7hoilx8shpi53/az6VDOJsO3
 2AA6rzDE1RsDUBaJva5+DwR6tnfGePGbTrFyEuGor9MUlFZCYhjtVwadAcd1x1kx+O3f
 94RweAdweJhyuLazu54oHXiHKlEHZLwkFOezx4IXoPnTbbYCx8a9flprD3MOicbqaqyM
 ldy8J+aCt8Ipiu3uYglVXQNS1S6zC1KDiGhMNUEZqSr3n7D7DA8xmbGHsLeizb3bl7o7
 P5mcI27C94m138mZNeBgUJvVrEyVxxoE4uLJ+rL4oNTgRaWBuzshLsYo9etrjkubvU4q
 ng3g==
X-Received: by 10.68.164.131 with SMTP id yq3mr25103275pbb.56.1385886731944;
 Sun, 01 Dec 2013 00:32:11 -0800 (PST)
Received: from [192.168.1.111] ([110.138.251.110])
 by mx.google.com with ESMTPSA id pl1sm114019257pbb.20.2013.12.01.00.32.08
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Sun, 01 Dec 2013 00:32:11 -0800 (PST)
Subject: Re: Speed up the query
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=iso-8859-1
From: Hengky Liwandouw <hengkyliwandouw@gmail.com>
In-Reply-To: <20131201071231.GA4780@tux>
Date: Sun, 1 Dec 2013 16:31:48 +0800
Cc: Andreas Kretschmer <akretschmer@spamfence.net>
Content-Transfer-Encoding: quoted-printable
Message-Id: <35F37CEE-43DA-4B06-9DB8-953A1998B21A@gmail.com>
References: <B097B80F-2D51-4F42-BC0D-5A38CBCD0C01@gmail.com>
 <20131201071231.GA4780@tux>
To: pgsql-performance@postgresql.org
X-Mailer: Apple Mail (2.1084)
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Andreas, sorry this is the correct analyse for the query.

This is the index i created :

CREATE INDEX tbltransaksi_idx10 ON public.tbltransaksi
 USING btree ((date_part('year'::text, tanggal)));

This is the analyse of the query

"Limit  (cost=3D346377.92..346380.42 rows=3D1000 width=3D376)"
"  Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, (sum(CASE =
WHEN (qry1.bulan =3D 1::double precision) THEN qry1.keluar ELSE 0::numeric =
END)), (sum(CASE WHEN (qry1.bulan =3D 2::double precision) THEN qry1.keluar=
 ELSE 0::numeric END)), (sum(CASE WH (...)"
"  CTE qry1"
"    ->  Hash Join  (cost=3D4444.64..62681.16 rows=3D766491 width=3D84)"
"          Output: tbltransaksi.tanggal, date_part('month'::text, (tbltrans=
aksi.tanggal)::timestamp without time zone), tblsupplier.id, tblsupplier.na=
ma, tbltransaksi.kodebarang, tblproduk.namabarang, tbltransaksi.keluar, CAS=
E WHEN (tbltransaksi.discount <=3D (...)"
"          Hash Cond: ((tbltransaksi.kodebarang)::text =3D (tblproduk.produ=
kid)::text)"
"          ->  Seq Scan on public.tbltransaksi  (cost=3D0.00..24702.53 rows=
=3D766491 width=3D29)"
"                Output: tbltransaksi.id, tbltransaksi.tanggal, tbltransaks=
i.kodebarang, tbltransaksi.masuk, tbltransaksi.keluar, tbltransaksi.satuan,=
 tbltransaksi.keterangan, tbltransaksi.jenis, tbltransaksi.harga, tbltransa=
ksi.discount, tbltransaksi.juali (...)"
"                Filter: ((tbltransaksi.jualid IS NOT NULL) OR ((tbltransak=
si.returjualid IS NOT NULL) AND (date_part('year'::text, (tbltransaksi.tang=
gal)::timestamp without time zone) =3D 2013::double precision)))"
"          ->  Hash  (cost=3D3380.52..3380.52 rows=3D85130 width=3D68)"
"                Output: tblproduk.namabarang, tblproduk.produkid, tblsuppl=
ier.id, tblsupplier.nama"
"                ->  Hash Join  (cost=3D21.68..3380.52 rows=3D85130 width=
=3D68)"
"                      Output: tblproduk.namabarang, tblproduk.produkid, tb=
lsupplier.id, tblsupplier.nama"
"                      Hash Cond: ((tblproduk.supplierid)::text =3D (tblsup=
plier.id)::text)"
"                      ->  Seq Scan on public.tblproduk  (cost=3D0.00..2188=
.30 rows=3D85130 width=3D51)"
"                            Output: tblproduk.produkid, tblproduk.namabara=
ng, tblproduk.hargajual, tblproduk.subkategoriid, tblproduk.createby, tblpr=
oduk.kodepromo, tblproduk.satuan, tblproduk.foto, tblproduk.pajak, tblprodu=
k.listingfee, tblproduk.supplier (...)"
"                      ->  Hash  (cost=3D14.08..14.08 rows=3D608 width=3D26=
)"
"                            Output: tblsupplier.id, tblsupplier.nama"
"                            ->  Seq Scan on public.tblsupplier  (cost=3D0.=
00..14.08 rows=3D608 width=3D26)"
"                                  Output: tblsupplier.id, tblsupplier.nama"
"  ->  Sort  (cost=3D283696.76..283888.39 rows=3D76650 width=3D376)"
"        Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang, (sum=
(CASE WHEN (qry1.bulan =3D 1::double precision) THEN qry1.keluar ELSE 0::nu=
meric END)), (sum(CASE WHEN (qry1.bulan =3D 2::double precision) THEN qry1.=
keluar ELSE 0::numeric END)), (sum(C (...)"
"        Sort Key: (sum(COALESCE(qry1.keluar, 0::numeric)))"
"        ->  GroupAggregate  (cost=3D221240.80..279494.13 rows=3D76650 widt=
h=3D376)"
"              Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.namabarang=
, sum(CASE WHEN (qry1.bulan =3D 1::double precision) THEN qry1.keluar ELSE =
0::numeric END), sum(CASE WHEN (qry1.bulan =3D 2::double precision) THEN qr=
y1.keluar ELSE 0::numeric END), sum( (...)"
"              ->  Sort  (cost=3D221240.80..223157.03 rows=3D766491 width=
=3D376)"
"                    Output: qry1.id, qry1.nama, qry1.kodebarang, qry1.nama=
barang, qry1.bulan, qry1.keluar"
"                    Sort Key: qry1.id, qry1.nama, qry1.kodebarang, qry1.na=
mabarang"
"                    ->  CTE Scan on qry1  (cost=3D0.00..15329.82 rows=3D76=
6491 width=3D376)"
"                          Output: qry1.id, qry1.nama, qry1.kodebarang, qry=
1.namabarang, qry1.bulan, qry1.keluar"


On Dec 1, 2013, at 3:12 PM, Andreas Kretschmer wrote:

> Hengky Liwandouw <hengkyliwandouw@gmail.com> wrote:
>>=20
>> But the problem is : when i change the where clause to :
>>=20
>> where jualid is not null or returjualid is not null
>> and extract(year from tanggal)=3D'2013')
>=20
> Try to create this index:
>=20
> create index xxx on public.tbltransaksi((extract(year from tanggal)))
> where jualid is not null or returjualid is not null;
>=20
> 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.
>=20
>=20
> Andreas
> --=20
> 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=B0, E 13.56889=
=B0
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance