Thread: The use of cpu_index_tuple_cost by the query planner

The use of cpu_index_tuple_cost by the query planner

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

EXPLAIN (ANALYZE,BUFFERS) SELECT max(abalance) FROM pgbench_accounts;

And it's returned:

Aggregate  (cost=57786.99..57787.00 rows=1 width=4) (actual time=1317.775..1317.776 rows=1 loops=1)"
  Buffers: shared hit=2656 read=30131
  ->  Seq Scan on pgbench_accounts  (cost=0.00..52786.99 rows=1999999 width=4) (actual time=0.062..683.919 rows=2000000 loops=1)
        Buffers: shared hit=2656 read=30131
Total runtime: 1317.813 ms


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:
SEQ SCAN = ( cpu_tuple_cost *  rows ) +  ( number of pages * seq_page_cost )
AGGREGATE = cpu_operator_cost * rows + SEQ SCAN

As I'm using default values for all parameters, they are set this way:
seq_page_cost=1
cpu_tuple_cost=0.01
cpu_operator_cost=0.0025

So,

SEQ SCAN = ( 0.01 * 1999999 ) + (  ( 2656+30131 )  * 1 ) = 52786.99
AGGREGATE = 0.0025 * 1999999 + 52786.99 = 57786.9875

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


Re: The use of cpu_index_tuple_cost by the query planner

From
Simon Riggs
Date:
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

Re: The use of cpu_index_tuple_cost by the query planner

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


Re: The use of cpu_index_tuple_cost by the query planner

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

On Wed, Jun 27, 2012 at 8:22 PM, Jeff Davis <pgsql@j-davis.com> wrote:
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