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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2012-06-27 20:37:31 | Re: The use of cpu_index_tuple_cost by the query planner |
Previous Message | Simon Riggs | 2012-06-27 17:54:58 | Re: Function Scan costs |