Re: Queries very slow and memory consumption too high

On Mon, Jun 22, 2009 at 9:48 PM, Saurabh Dave <saurabhdave(at)gmail(dot)com> wrote:

> Thanks Albe,
>
> "You'll have to analyze *what* is slow."
>
> That is an excellent question, which I am struggling to answer.
> As per your recommendation I moved DB Server on a remote machine and made
> following changes:
> 1. Postgres version 8.3.7.1 instead of 8.2
> 2. DB encoding changed from UTF-8 to SQL_ANSCII
>
> Now I see the performance of my application to be quite fast, I need to
> observe it for some more time though.
>
> But over the top, do you think that changing the encoding would make
> difference?
>

Yes, but you needed to analyze what is slow to determine this. Simply
changing things and without knowing why doesn't help us determine the
problem.

But back to encoding. There are situations where the default setup will not
use an index if the encoding is not correct.

So please analyze the slow queries with explain analyze
Also be aware that the driver uses prepared statements so

explain analyze select * from foo where blah=1 is different from

prepare t1 as select * from foo where blah = ?
explain analyze execute foo(1)

Dave

>
> Thanks,
> Saurabh
>
>
> On Mon, Jun 22, 2009 at 1:02 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:
>
>> Saurabh Dave wrote:
>> > I am using Postgres 8.2.10 + Hibernate + c3p0 connection pooling.
>> > For our application we support MySQL + Oracle + MSSQL + Postgres.
>> >
>> > With all the other databases our application is working quite
>> > fine, but with Postgres after a day queries are becoming
>> > extremely slow and it seems to be taking up all the available memory.
>> >
>> > Please let me know what could be the issue. I am new to
>> > Postgres, so any help would be greatly appreciated.
>>
>> You'll have to analyze *what* is slow.
>>
>> Is it the database server or some other component?
>>
>> It helps to have the various components installed on different machines.
>>
>> Once you have found out what is slow, drill down there.
>>
>> If it turns out to be the database and need help, you'll have to provide
>> EXPLAIN ANALYZE output of a sample query that runs fast in the
>> beginning and slow later.
>>
>> Yours,
>> Laurenz Albe
>>
>
>