Message-ID: <1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com>
In-Reply-To: 
 <CACBfhZMajgneA6x4r63bNayYtsfpmYV8UUOhBRcr5h-emmgxKw@mail.gmail.com>
References: 
 <CACBfhZPO1GJv4nVunPXGA2EZq923On-CBGtxyhtQYAaSMCuBEQ@mail.gmail.com>
	<CAF-3MvOjY17pVZXSWgYDB5dMy-B07KiVYexR4RRutxZyXeKjbA@mail.gmail.com>
	<CACBfhZNBwjB=3xQrvqy6ManJUaRyKEOoV8dMv7jJYPtXn9XFcQ@mail.gmail.com>
	<CACBfhZNr5qnMN0=8hp1Fb3hA8jwoEXnJAahua1JT5f5gXT9tuA@mail.gmail.com>
	<4EAFC29A.40509@engene.se> <24830.1320154980@sss.pgh.pa.us>
	<CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com>
	<4EB2ACEF.8090305@gmx.at>
	<65a8332e0128eda27f0345da80946dc6.squirrel@sq.gransy.com>
	<4EB2F49F.3080903@gmx.at>
	<CACBfhZP7OUL+UpauYYSuNbOG-tGbB+TwKzhY7FkwrdvxDaM8Jg@mail.gmail.com>
	<c5df85c09ae21f15f73e510f51b3c128.squirrel@sq.gransy.com>
	<CACBfhZMajgneA6x4r63bNayYtsfpmYV8UUOhBRcr5h-emmgxKw@mail.gmail.com>
Date: Tue, 8 Nov 2011 13:40:25 +0100
Subject: Re: Performance Problem with postgresql 9.03, 8GB
	RAM,Quadcore Processor Server--Need help!!!!!!!
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Mohamed Hashim" <nmdhashim@gmail.com>
Cc: "Tomas Vondra" <tv@fuzzy.cz>, "Mario Weilguni" <roadrunner6@gmx.at>,
	pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

On 8 Listopad 2011, 13:15, Mohamed Hashim wrote:
> Hi Sam,Tomas
>
> In my first post i have mentioned all how much shared (shared buffers,
> effective cache size, work mem, etc.) and my OS and hardware information
> and what are the basic settings i have changed

Sorry, I've missed that first message - the archive did not list it for
some reason.

> and regarding Explain analyze i gave one sample query because if i tune
> that particular table which has records almost 16crore i thought my
> problem
> will solve...

OK, so the problem is either the WHERE conditions referencing the array
column, or a bug in 9.0.x.

Can't help you with the upgrade issue, but using an array like this is a
bad design and will cause you all sorts of problems - why are you not
using regular columns, anyway?

I see you usually reference source_detail[1] - what is the expected
selectivity of this condition? What portion of the table matches it? How
many possible values are there?

And it does not make sense to me to partition by date when you're not
querying the data by date.

Try this (one by one):

1) CREATE INDEX src_idx ON stk_source(source_detail[1]) for each partition
2) add a regular column source_detail_val with the value of
source_detail[1] and create an index on it
3) repartition the table by source_detail[1] instead of date

Tomas