Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!

On 8 Listopad 2011, 4:21, Mohamed Hashim wrote:
> Hi all,
>
> Thanks for all your responses.
>
> Sorry for late response
>
> Earlier we used Postgres8.3.10 with Desktop computer (as server) and
> configuration of the system (I2 core with 4GB RAM) and also the
> application
> was slow i dint change any postgres config settings.
>
> May be because of low config We thought the aplication is slow so we opted
> to go for higher configuration server(with RAID 1) which i mentioned
> earlier.
>
> I thought the application will go fast but unfortunately there is no
> improvement so i tried to change the postgres config settings and trying
> to
> tune my queries wherever possible but still i was not able
> to..........improve the performance..

As Sam Gendler already wrote, we really can't help you until you post all
the relevant info. So far we've seen a single EXPLAIN ANALYZE output and
very vague description of the hardware.

We need to know more about the hardware and the basic config options
(shared buffers, effective cache size, work mem, etc.). We need to know
how much memory is actually available to PostgreSQL and page cache (how
much is consumed by the application - as I understand it it runs on the
same machine). We need to know what OS it's running on, and we need to see
iostat/vmstat output collected when the app is slow.

Please read this and perform the basic tuning (and let us know what values
you've used):

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Also post

> So will it helpful if we try GIST or GIN for integer array[] colum
> (source_detail) with enable_seqscan=off and
> default_statistics_target=1000?

This is severely premature - it might help, but you should do the basic
tuning first. It might actually cause you more trouble. You've already
done this mistake - fixing something withouth veryfying it's actually a
problem - by requesting a RAID1 config. Don't do that mistake again.

Tomas