Re: Bug in query planer ?

Hi,

On Wednesday 01 February 2006 18:19, you wrote:
> Clifford Wolf <clifford(dot)wolf(at)linbit(dot)com> writes:
> > However, it would be great to have get_restriction_variable() and
> > get_attstatsslot() extended so they can pass the most common values
> > from the statistics cache thru expressions, as described in my earlier
> > mail.
>
> This would only be helpful if the most-common-values list describes
> practically all of the column population, which isn't typically the case.

Not more than it is the case already for the simple 'variable = const'.
(Or am I looking at the wrong eqsel() function?)

Even when there is no match in the most-common-values list, the list can be
used to determine a more realistic selectivity than DEFAULT_EQ_SEL (as it is
done already in the 'variable = const' cases now).

For linear functions it would even be possible to use the histogram_bounds
to get a good idea of the selectivity. But that is an optimization that even
is not implemented for simple 'variable = const' cases yet.

> In any case I'm not sure why you're resistant to maintaining an index
> on an expression that you are frequently querying by --- that index
> could have more direct use than just cueing ANALYZE what to figure
> statistics on.

In this case definitely not. The only effect that index has is to show the
query planner that it is a bad idea to use the indexed expression as inner
clause.

In the good query plans that expression is evaluated on the heap in a
sequential scan (reduces a set of ~10 tuples to ~8 tupels). The index
is never used.

And this index just sovles this one extreme case, there are hundrets of
queries with very suboptimal query plans because of that. I don't want to
create hunderts of indexes just to make sure that the index is not used.

.. I would do that (auto-generate hundrets of indexes from our slow-query
log) when there would be some kind of semi-index type which just collects
statistics on ANALYZE. But afaik this is not possible with Postgres right
now. Creating that many real indexes would pretty sure slow down inserts
and updates that much that it is better to live with bizare query plans
for the selects..

yours,
- clifford

--
: Clifford Wolf                      Tel +43-1-8178292-00 :
: LINBIT Information Technologies GmbH          Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria    http://www.linbit.com :