Re: Optimizer produces wildly different row count estimate depending on casts

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> The optimizer has no idea about the selectivity of
> the clauses involving
> current_date, since it hasn't got a constant value
> to compare to the
> statistical histogram.  (In the case of expressions
> involving
> current_date and similar functions, it might be
> reasonable to compute
> the current value and use that as an estimate, but
> I'm unsure of the
> conditions under which that's safe.  In any case
> there is no such code
> at present.)  So for query #1 we have a
> hopefully-pretty-good estimate
> for "txn_date > '07-FEB-2004'", and then we knock
> that down by an
> arbitrary percentage because we don't have a clue
> about "txn_date <=
> current_date"; which is why the estimate is too
> small.  In query #2 we
> are, plain and simply, guessing.  The optimizer can
> however see that
> this is a range constraint on txn_date, and the
> default guess in such
> cases is chosen to favor an indexscan.
>

Interesting.  Thanks for the clarification.

>
> Neither clause is indexable, but at least the
> planner can see that it's
> a range constraint on txn_date::timestamp, so you
> get a smaller
> guesstimate.
>

That makes 100% sense.  Thanks again.

Regards,

Shelby Cain

__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you�re looking for faster
http://search.yahoo.com