Tom Lane wrote:
Mark Kirkwood <[EMAIL PROTECTED]> writes:
the costs of paths using these indexes are
quite similar, so are quite sensitive to (some) parameter values.
They'll be exactly the same, actually, as long as the thing predicts
exactly one row retrieved. So it's quasi-random which plan you get.
btcostestimate needs to be improved to understand that in multicolumn
index searches with inequality conditions, we may have to scan through
tuples that don't meet all the qualifications. It's not accounting for
that cost at the moment, which is why the estimates are the same.
I see some small differences in the numbers - I am thinking that these
are due to the calculations etc in cost_index(). e.g:
create_index_paths : index oid 12616389 (test_id2)
cost_index : cost=2.839112 (startup_cost=0.000000 run_cost=2.839112)
: tuples=1.000000 cpu_per_tuple=0.017500
: selectivity=0.000002
: run_index_tot_cost=2.003500 run_io_cost=0.818112)
create_index_paths : index oid 12616388 (test_id1)
cost_index : cost=2.933462 (startup_cost=0.002500 run_cost=2.930962)
: tuples=1.000000 cpu_per_tuple=0.010000
: selectivity=0.000002
: run_index_tot_cost=2.008500 run_io_cost=0.912462
Where:
run_index_tot_cost=indexTotalCost - indexStartupCost;
run_io_cost=max_IO_cost + csquared * (min_IO_cost - max_IO_cost)
selectivity=indexSelectivity
Hmmm ... so it's only the selectivity that is the same (sourced from
index->amcostestimate which I am guessing points to btcostestimate), is
that correct?
cheers
Mark
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
