Thread: The use of cpu_index_tuple_cost by the query planner

Hello PostgreSQL users,

I'm trying to understand the use of PostgreSQL tuning parameters by the query planner's cost estimator. I'm trying to use simple queries to understand how these parameters would affect the estimated cost. However, I haven't still been able to see the cpu_index_tuple_cost.

I'm going to give an example of how I was able to deduct cpu_operator_cost and cpu_tuple_cost. I have a PGbench database, in which I have run the following query:


And it's returned:



So basically this query performs a SEQ SCAN and then an AGGREGATE. As I've seen in PostgreSQL source code ( could not find anywhere else ). The estimated cost for these two operations are:

As I'm using default values for all parameters, they are set this way:

So,

As seen on the plan, these numbers are correct.
I tried to find a similar way to get the cpu_index_tuple_cost, but I couldn't. Does anybody know of any simple query that could help me extracting this parameter?


Regards,

Antonio Carlos Furtado

On 27 June 2012 21:28, Antonio Carlos Salzvedel Furtado Junior
<acsfj08@inf.ufpr.br> wrote:

> I'm trying to understand the use of PostgreSQL tuning parameters by the
> query planner's cost estimator. I'm trying to use simple queries to
> understand how these parameters would affect the estimated cost. However, I
> haven't still been able to see the cpu_index_tuple_cost.

Your best hope is to read the source code. Things will become much clearer.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Wed, 2012-06-27 at 21:37 +0100, Simon Riggs wrote:
> On 27 June 2012 21:28, Antonio Carlos Salzvedel Furtado Junior
> <acsfj08@inf.ufpr.br> wrote:
>
> > I'm trying to understand the use of PostgreSQL tuning parameters by the
> > query planner's cost estimator. I'm trying to use simple queries to
> > understand how these parameters would affect the estimated cost. However, I
> > haven't still been able to see the cpu_index_tuple_cost.
>
> Your best hope is to read the source code. Things will become much clearer.

Right, the cost model is too complex to really understand just running
EXPLAIN.

The tuning parameters are translated to global variables
in ./src/backend/utils/misc/guc.c. From there, you can search for
references to the global variables (often with very similar names to the
tuning parameter) and find out how they are used in the cost model.

Regards,
    Jeff Davis

Thanks,

I had to look in the soure code for the other parameters as well.  I was trying to create some queries because I was interested in the relation between estimated cost and actual time.

Regards,

Antonio Carlos Furtado