Re: [HACKERS] longer-term optimizer musings

> As with my previous posts, this is most likely not a general solution,
> it's just an idea that works (very well) for the query I am looking
> at, and has some general applicability.  I am sure that the above
> ignores a number of "bigger picture" issues, but it does help the
> particular query I care about.
> 
> Also note that none of this actually speeds up even my query, it only
> makes the optimizer estimate much closer to the actual query cost
> (which is what I care about for the work I am doing).
> 
> Maybe this will be of help in any future work on the optimizer.  Maybe
> it is simply the rantings of a lunatic.

Interesting.  The problem I see is that trying to do a char(20) column
with min(A) and max(B) can have 256^19 possible unique values from A to
B, so it kind if kills many general cases.  Floats have the same
problem.

A nice general fix would be to assume GROUP BY/AGG returns only 10% of
the existing rows.  I don't even know if an Aggregate without a group by
knows it only returns one row.  Oops, I guess not:
test=> explain select max(relpages) from pg_class;NOTICE:  QUERY PLAN:Aggregate  (cost=2.58 size=0 width=0)  ->  Seq
Scanon pg_class  (cost=2.58 size=48 width=4)
 
Basically, there are some major issues with this optimizer.  Only in pre
6.5 have we really dug into it and cleaned up some glaring problems. 
Problems that were so bad, if I had know how bad they were, I would
certainly have started digging in there sooner.

We have even general cases that are not being handled as well as they
should be.  We just fixed a bug where "col = -3" was never using an
index, because -3 was being parsed as prefix "-" with an operand of 3,
and the index code can only handle constants.

Yes, we have some major things that need cleaning.  I have updated
optimizer/README to better explain what is happening in there, and have
renamed many of the structures/variables to be clearer.  I hope it
helps someone, someday.

So I guess I am saying that your ideas are good, but we need to walk
before we can run with this optimizer.

I am not saying the optimizer is terrible, just that it is complex, and
has not had the kind of code maintenance it needs.

--  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