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