> 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