sorry, for now, work_mem=100MB
On Mon, Dec 2, 2013 at 12:33 AM, Hengky Lie wrote:
> Dear Torsten and friends,
>
> This is another good case to analyse why the query performance is not the
> same :
>
> There are 2 query :
> (1)
>
> with qry1 as (
> select subkategori, kodebarang as produkid, nam
Dear Torsten and friends,
This is another good case to analyse why the query performance is not the
same :
There are 2 query :
(1)
with qry1 as (
select subkategori, kodebarang as produkid, namabarang, keluar,
tbltransaksi.modal*keluar as ttlmodal,
case
when tbltransaksi.discount<=100 t
Thanks Kevin. You are absolutely right. I should use parentheses, it is
what i want for the query to do.
It also increasing processing time to 5.444 seconds. Should be okay i think.
On Sun, Dec 1, 2013 at 11:02 PM, Kevin Grittner wrote:
> Hengky Liwandouw wrote:
>
> > where jualid is not null
Hengky Liwandouw wrote:
> where jualid is not null
> and extract(year from tanggal)='2013')
> 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')
>
> (there is additional or returjualid is not
Thanks a lot Andreas :)
The tbltransaksi size = 263MB
Total database size = 1277MB
Quite small for so many records store in it.
This group really helpfull.
On Dec 1, 2013, at 8:29 PM, Andreas Kretschmer wrote:
> Hengky Liwandouw wrote:
>
>>
>> For Mat : what command i can use to show how b
Hengky Liwandouw wrote:
>
> For Mat : what command i can use to show how big the tables in MB ?
http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.
Torsten Förtsch wrote:
> I'd try 2 things:
>
> 1) set work_mem to ~100Mb. You don't have to do that globally in
> postgresql.conf. You can set it for the current session only.
>
> set work_mem to '100MB';
>
> Then run your query.
>
> 2) change the common table expression to a subquery:
Yeah
Dear All,
Thanks for the suggestion ! I tried to change the work_mem and the query only
needs 4.9 sec to display the result !
Torsten, your 2nd option didnt work with this error :
ERROR: syntax error at or near "discount"
LINE 1: ...rang, keluar, case when discount<
On 01/12/13 10:07, Hengky Liwandouw wrote:
> with qry1 as
> (select tanggal, extract(month from tanggal) as bulan, tblsupplier.id, nama,
> kodebarang, namabarang, keluar,
> case when discount<=100 then
> keluar*(harga -(discount/100*harga))
> when tbltransaksi.discount>100
Hello,
your problem seems to arises from the sort that id sone to
disk :
" -> Sort (cost=221247.80..223164.10 rows=766519 width=376)
(actual time=50731.687..54455.528 rows=737662 loops=1)"
"Sort Key: qry1.id, qry1.nama, qry1.kodebarang,
qry1.namabar
Ok, i just recreate the index :
CREATE INDEX tbltransaksi_idx10
ON tbltransaksi
USING btree
(date_part('year'::text, tanggal))
WHERE jualid IS NOT NULL OR returjualid IS NOT NULL;
(PGAdminIII always convert extract(year from tanggal) to
date_part('year'::text,tanggal))
This is the produ
Hengky Liwandouw 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
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=346377.92..346380.42 rows=1000 width=376)"
" Output:
13 matches
Mail list logo
