> 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 Scan on 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.
Also, let me not discourage you.
We are just learning about the optimizer, we welcome any ideas that you
may have. I also enjoy discussing the issues, becuase it give me a
sounding-board for future coding.
Keep it up.
-- 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