Thread: Changing tuning parameters with EXPLAIN

Changing tuning parameters with EXPLAIN

From
Antonio Carlos Salzvedel Furtado Junior
Date:
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

Re: Changing tuning parameters with EXPLAIN

From
Jeff Davis
Date:
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


Re: Changing tuning parameters with EXPLAIN

From
Antonio Carlos Salzvedel Furtado Junior
Date:
Thanks for the explanation.
I think I misunderstood the SET behaviour. It's OK if only my session is affected.

Regards,
Antonio Carlos Furtado

On Mon, Jun 4, 2012 at 6:01 PM, Jeff Davis <pgsql@j-davis.com> wrote:
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