From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | "Van Der Berg, Stefan" <SvanderBerg(at)fnb(dot)co(dot)za>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Strange performance problem with query |
Date: | 2014-09-15 13:25:44 |
Message-ID: | 1410787544.68356.YahooMailNeo@web122306.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"Van Der Berg, Stefan" <SvanderBerg(at)fnb(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Van Der Berg, Stefan | 2014-09-16 06:51:07 | Re: Strange performance problem with query |
Previous Message | Van Der Berg, Stefan | 2014-09-15 09:38:30 | Strange performance problem with query |