pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tv@fuzzy.cz>
To: Mohamed Hashim <nmdhashim@gmail.com>
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: Mario Weilguni <roadrunner6@gmx.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Date: Tue, 8 Nov 2011 13:40:25 +0100
Message-ID: <1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com> (raw)
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>

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




view thread (23+ messages)  latest in thread

Message-ID: <1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com>
Permalink:  ../1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com/
Also on:    postgresql.org/message-id/1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tv@fuzzy.cz, nmdhashim@gmail.com, roadrunner6@gmx.at
  Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
  In-Reply-To: <1f1660cbba9479276aa885f027cbbf0d.squirrel@sq.gransy.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox