Thread: Changing tuning parameters with EXPLAIN

Hello PostgreSQL users,

I've been working with the estimated cost from EXPLAIN. I was wondering if  I could change the tuning parameters, to produce new estimated costs, without interfering with the database workload. Is it possible?

I'm changing these parameters by executing SQL queries ("SET"). The problem is that once I change these parameters, the query optimizer will start using these values to build their query plans.

Cordially,

Antonio Carlos Furtado

On Mon, 2012-06-04 at 17:49 -0300, Antonio Carlos Salzvedel Furtado
Junior wrote:
> Hello PostgreSQL users,
>
> I've been working with the estimated cost from EXPLAIN. I was
> wondering if  I could change the tuning parameters, to produce new
> estimated costs, without interfering with the database workload. Is it
> possible?
>
> I'm changing these parameters by executing SQL queries ("SET"). The
> problem is that once I change these parameters, the query optimizer
> will start using these values to build their query plans.

If you issue a SET, it only affects queries executed in your connection
(the session). So this is safe for EXPLAINing queries. If you do a "SET
LOCAL" within a transaction, it will only affect that transaction.

To make the values persist and get picked up by other connections, you
need to set them in postgresql.conf and send a SIGHUP (or restart, of
course).

Regards,
    Jeff Davis

Thanks for the explanation.
I think I misunderstood the SET behaviour. It's OK if only my session is affected.

Regards,
Antonio Carlos Furtado