Re: [HACKERS] optimizer and type question

> Erik Riedel <riedel+@CMU.EDU> writes:
> > [ optimizer doesn't find relevant pg_statistic entry ]
> 
> It's clearly a bug that the selectivity code is not finding this tuple.
> If your analysis is correct, then selectivity estimation has *never*
> worked properly, or at least not in recent memory :-(.  Yipes.
> Bruce and I found a bunch of other problems in the optimizer recently,
> so it doesn't faze me to assume that this is broken too.

Yes.  Originally, pg_statistic was always empty, and there was no
pg_attribute.attdisbursion.

I added proper pg_attribute.attdisbursion processing.  In fact, our TODO
list has(you can see it on our web page under documentation, or in
/doc/TODO):
* update pg_statistic table to remove operator column

What I did not realize is that the selectivity code was still addressing
that column.  We either have to populate is properly, or throw it away. 
The good thing is that we only use "<" and ">" to compute min/max, so we
really don't need that operator column, and I don't know what I would
put in there anyway.

I realized "<" optimization processing was probably pretty broken, so
this is no surprise.

What we really need is some way to determine how far the requested value
is from the min/max values.  With int, we just do (val-min)/(max-min). 
That works, but how do we do that for types that don't support division.
Strings come to mind in this case.  Maybe we should support string too,
and convert all other types to string representation to do the
comparison, though things like date type will fail badly.

My guess is that 1/3 is a pretty good estimate for these types.  Perhaps
we should just get int types and float8 types to work, and punt on the
rest.

> I think you've found a can of worms here.  Congratulations ;-)

I can ditto that.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026