Re: Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:33 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Hi!
> >Could you also post the results of the following query?
> >SELECT name, current_setting(name), source
> >FROM pg_settings
> >WHERE source NOT IN ('default', 'override');
> In real server where problem is:
>
>
> 21 shared_buffers 2400MB configuration file
>

What are effective_cache_size and work_mem set to? The defaults? They are
good candidates to be increased. effective_cache_size could be set to (for
example) 10GB, depending on how much memory gets consumed by the other
application(s) running on that server.

The EXPLAIN ANALYZE plan of your query will show if work_mem needs to be
increased, as there will be a line saying something like "External merge:
disk sort" (or something like that, can't recall the exact message off the
top of my head).

>
> In development computer from where explain was posted and problem with
> copy of database also occurs:
>
> "shared_buffers";"128MB";"configuration file"
>

You likely want to bump that up closer to 1GB.

>
> > Also, what is the total memory in the server?
>

> In devel computer where tests are performed, 4 GB
> Real server has 16 GB RAM
> Real server is for Postgres for this database and ASP.NET MVC3
> application which uses this same database from postgres.
>