Re: Strange performance problem with query

"Van Der Berg, Stefan" <SvanderBerg@fnb.co.za> wrote:

> I get a similar plan selected on the original query if I set

> enable_seqscan to off. I much prefer the second result.
> My questions are:
> 1. Why is this happening?

Your cost factors don't accurately model actual costs.

> 2. How can I encourage the behavior of the second query without
> changing the original query?

You didn't give enough information to really give solid advice, but
when people see what you are seeing, some common tuning needed is:

Set shared_buffers to about 25% of system RAM or 8GB, whichever is
lower.

Set effective_cache_size to 50% to 75% of system RAM.

Set work_mem to about 25% of system RAM divided by max_connections.

If you have a high cache hit ratio (which you apparently do) reduce
random_page_cost, possibly to something near or equal to
seq_page_cost.

Increase cpu_tuple_cost, perhaps to 0.03.

You might want to play with the above, and if you still have a
problem, read this page and post with more detail:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

> Is there some column level setting I can set?

The statistics looked pretty accurate, so that shouldn't be
necessary.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company