From: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | riedel+(at)CMU(dot)EDU (Erik Riedel) |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] longer-term optimizer musings |
Date: | 1999-03-24 04:08:14 |
Message-ID: | 199903240408.XAA26156@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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 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.
--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-03-24 04:13:24 | Re: [HACKERS] longer-term optimizer musings |
Previous Message | Bruce Momjian | 1999-03-24 03:53:59 | Re: [HACKERS] PostgreSQL LOGO (was: Developers Globe (FINAL)) |