Re: Postgres Optimizer is not smart enough?

Tom Lane wrote:
> Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> 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