Re: Strange performance problem with query

Hi Kevin,

Thanks for the advice.

I opted for setting the random_page_cost a bit lower, as that made the
most sense in the context of the current setup where there is quite a
high cache hit ratio. Is 97% high enough?:

=# SELECT
'cache hit rate' AS name,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS
ratio
FROM pg_statio_user_tables;
name | ratio
----------------+------------------------
cache hit rate | 0.97344836172381212996

When I set the random_page_cost down from 4 to 2, the query plan changes
to the faster one.

Kind Regards
Stefan

Cell : 072-380-1479
Desk : 087-577-7241

On 2014/09/15 03:25 PM, Kevin Grittner wrote:
> "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
To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser:
https://www.fnb.co.za/disclaimer.html

If you are unable to access the Disclaimer, send a blank e-mail to
firstrandbankdisclaimer(at)fnb(dot)co(dot)za and we will send you a copy of the Disclaimer.