Skip site navigation (1) Skip section navigation (2)

The use of cpu_index_tuple_cost by the query planner

From: Antonio Carlos Salzvedel Furtado Junior <acsfj08(at)inf(dot)ufpr(dot)br>
To: pgsql-novice(at)postgresql(dot)org
Subject: The use of cpu_index_tuple_cost by the query planner
Date: 2012-06-27 20:28:48
Message-ID: CAERqmVqTHjGETfLPDNAtcqOqoV9XsQOX0UKZ+8SJe=X=KccLmg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Simon RiggsDate: 2012-06-27 20:37:31
Subject: Re: The use of cpu_index_tuple_cost by the query planner
Previous:From: Simon RiggsDate: 2012-06-27 17:54:58
Subject: Re: Function Scan costs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group