pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Kirkwood <markir@coretech.co.nz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Litao Wu <litaowu@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres Optimizer is not smart enough?
Date: Thu, 13 Jan 2005 22:02:15 +1300
Message-ID: <41E63917.2070906@coretech.co.nz> (raw)
In-Reply-To: <4337.1105583973@sss.pgh.pa.us>
References: <20050112222506.32084.qmail@web13124.mail.yahoo.com>
	<41E5AF3F.2080100@coretech.co.nz>
	<4337.1105583973@sss.pgh.pa.us>

Tom Lane wrote:
> Mark Kirkwood <markir@coretech.co.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





view thread (8+ messages)  latest in thread

Message-ID: <41E63917.2070906@coretech.co.nz>
Permalink:  ../41E63917.2070906@coretech.co.nz/
Also on:    postgresql.org/message-id/41E63917.2070906@coretech.co.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: markir@coretech.co.nz, tgl@sss.pgh.pa.us, litaowu@yahoo.com
  Subject: Re: Postgres Optimizer is not smart enough?
  In-Reply-To: <41E63917.2070906@coretech.co.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox