Re: Enabling and disabling run time configuration parameters.

On Thu, 5 Jun 2003, Yusuf wrote:

> I have discovered that I could optimize queries by adjusting the
> following parameters such as enable_seqscan, enable_hashjoin,
> enable_mergejoin and enable_nestloop.

Setting those to get a fast query is the brute force method. It works,
but at some cost of flexibility.

Have you run vacuum full and analyze? If not, the planner has no clue how
to decide on which plans to choose.

> Is it a good idea, to temporarily adjust those values before running a
> query to spend up the execution time? I've searched online and wasn't
> able to find articles about it.

Yes, it's a great idea to do that in testing. No, it's a bad idea to rely
on them in production.

> I need to speed up an enterprise application that I'm working on, and I
> wouldn't want to screw things up.

Then you'll want to tune your databases cost estimates so it makes the
right decision.

> My plan is for every query that could be optimized by adjusting
> parameters: I'll enable parameters that'll speed it up, run the query,
> then set the parameters back to their default values.

That's a good plan as long as you go the extra step of making the changes
to the cost parameters so that the planner chooses correctly between the
different options it has.

Every server has different performance characteristics. A machine with 1
gig of RAM and 18 drives in a large RAID 1+0 is going to handle random
page access a lot better than a machine with 256 Meg ram and a single IDE
hard drive.

The values you need to look at are these:

random_page_cost
cpu_index_tuple_cost
cpu_operator_cost
cpu_tuple_cost
effective_cache_size

They are covered in detail in the docs here:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=runtime-config.html

I'm gonna go offline and write a quick tutorial on tuning your database to
your server. Look for a preliminary version today or tomorrow.

Set effective cache size to approximately the size of all kernel cache
buffer/pagesize (8192 for most pgsql setups).

Then tune the *_cost options so the planner picks the right plan each
time.