On Sat, Nov 29, 2008 at 6:43 PM, Andrus <[EMAIL PROTECTED]> wrote:
>> I'm still not sure why the planner chose to sort rather than hash with
>> oversized work_mem (is there an implied order in the query results I
>> missed?).
>
> Group by contains decimal column exchrate. Maybe pg is not capable to
I'm still not sure why the planner chose to sort rather than hash with
oversized work_mem (is there an implied order in the query results I
missed?).
Group by contains decimal column exchrate. Maybe pg is not capable to use
hash with numeric datatype.
My guess is that this query can still
I it seems that slowness is caused by grouping by column
exchrate numeric(13,8)
exchrate has different values in few rows.
It has usually value 0
In this sample query it is always 0.
I tried not change exchrate with
nullif( bilkaib.EXCHRATE,0)
but this does not up speed query, no idea wh
On Fri, Nov 28, 2008 at 12:51 PM, Scott Carey <[EMAIL PROTECTED]> wrote:
> I'm not sure that postgres allocates the whole work_mem each time, and in any
> event, the OS won't try and allocate to RAM until the page is actually used.
> My experience is that oversized work_mem doesn't hurt until it
I it seems that slowness is caused by grouping by column
exchrate numeric(13,8)
if this column is excluded, query takes 12 seconds
if this column in present, query takes 27 (!) seconds.
How to fix this ?
Andrus.
set search_path to firma2,public;
SET work_mem = 2097151 ;
explain analyze SELECT
Application should work with any server starting at 8.1 with any RAM size
(probably starting at 1 GB).
How to find safe value which does not cause error in SET work_mem command ?
If I use 2 GB maybe this can cause excaption when running in server with 1
GB RAM where this setting may be not all
purely in memory which reduced the number of compares required. But that is
just a guess.
From: Scott Marlowe [EMAIL PROTECTED]
Sent: Friday, November 28, 2008 10:24 AM
To: Andrus
Cc: Scott Carey; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] I
On Fri, Nov 28, 2008 at 10:58 AM, Andrus <[EMAIL PROTECTED]> wrote:
>
> SET work_mem = 2097151 (this is max allowed value) or SET work_mem = 97151
> decreases query time from 12 seconds to 9 seconds.
Setting work_mem so high that it allocates memory that isn't there
WILL slow down your queries, b
Scott,
Thank you.
The below query is spending most of its time in the sort, or perhaps the
complicated check condition before it.
The explain has a 8 second gap in time between the 2.8 seconds after the
Hash Left Join and before the Sort. I'm guessing its hidden in the sort.
You can get the pl
bject: [PERFORM] Increasing GROUP BY CHAR columns speed
Group by using CHAR columns takes abnormally big time.
How to speed it ?
Andrus.
8.1.4, cluster locale is en-us, db encoding is utf-8
set search_path to firma2,public;
explain analyze SELECT
CASE WHEN bilkaib.raha='EEK' THEN 0 ELSE bi
Group by using CHAR columns takes abnormally big time.
How to speed it ?
Andrus.
8.1.4, cluster locale is en-us, db encoding is utf-8
set search_path to firma2,public;
explain analyze SELECT
CASE WHEN bilkaib.raha='EEK' THEN 0 ELSE bilkaib.id END,
bilkaib.DB,
CASE WHEN dbkonto.objekt1='+' THEN
11 matches
Mail list logo
