optimizer is broken
I am working on it now, but it is currently not working properly.
--
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, Pennsylvania 19026I have fixed the optimizer, and it is working properly again, and faster
too.
--
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, Pennsylvania 19026
On Fri, 12 Feb 1999, Bruce Momjian wrote:
I have fixed the optimizer, and it is working properly again, and faster
too.
What about cheaper? 8^)
--
Todd Graham Lewis 32���49'N,83���36'W (800) 719-4664, x2804
******Linux****** MindSpring Enterprises tlewis@mindspring.net
"Those who write the code make the rules." -- Jamie Zawinski
On Fri, 12 Feb 1999, Todd Graham Lewis wrote:
On Fri, 12 Feb 1999, Bruce Momjian wrote:
I have fixed the optimizer, and it is working properly again, and faster
too.What about cheaper? 8^)
"You too can get your fixed and faster optimizer for *today only* at the
*low low* price of...$9.95...not available in stores, only calling out
exclusive, will last for the next 6 minutes, toll free number. Do *NOT*
be the last on your block to own one of these"
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Fri, 12 Feb 1999, Todd Graham Lewis wrote:
On Fri, 12 Feb 1999, Bruce Momjian wrote:
I have fixed the optimizer, and it is working properly again, and faster
too.What about cheaper? 8^)
"You too can get your fixed and faster optimizer for *today only* at the
*low low* price of...$9.95...not available in stores, only calling out
exclusive, will last for the next 6 minutes, toll free number. Do *NOT*
be the last on your block to own one of these"
Let me also mention I spent almost 3 hours on the phone with Tom Lane
helping me on this. Thanks to Tom.
--
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, Pennsylvania 19026
Off topic. But hey, it's saturday morning.
Scrappy wrote...
On Fri, 12 Feb 1999, Todd Graham Lewis wrote:
On Fri, 12 Feb 1999, Bruce Momjian wrote:
I have fixed the optimizer, and it is working properly again, and faster
too.What about cheaper? 8^)
"You too can get your fixed and faster optimizer for *today only* at the
*low low* price of...$9.95...not available in stores, only calling out
exclusive, will last for the next 6 minutes, toll free number. Do *NOT*
be the last on your block to own one of these"
One 'zen of engineering' lesson I was taught was when many years ago a
mentor of mine wrote on the board:
GOOD
FAST
CHEAP
pick any two
PostgreSQL, it seems, may be an exception to this rule.
-- cary
Import Notes
Resolved by subject fallback
I have fixed the optimizer, and it is working properly again, and faster
too.-- 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, Pennsylvania 19026
Looks good Bruce.
Here are some explain results from the 6.4.2 release and the development tree.
Postgres 6.4.2:
---------------
QUERY: EXPLAIN
SELECT hosts.host,
passwords.login,
passwords.uid,
groups.grp,
passwords.gecos,
passwords.home,
passwords.shell
FROM hosts,
passwords,
groups
WHERE hosts.host_id = passwords.host_id AND
groups.host_id = passwords.host_id AND
groups.gid = passwords.gid;
NOTICE: QUERY PLAN:
Merge Join (cost=30894.02 size=2358855 width=108)
-> Nested Loop (cost=20459.89 size=278240 width=84)
-> Index Scan using hosts_pkey on hosts (cost=13.90 size=198 width=16)
-> Index Scan using passwords_pkey on passwords (cost=103.26
size=154973 width=68)
-> Seq Scan (cost=20459.89 size=0 width=0)
-> Sort (cost=164.82 size=0 width=0)
-> Seq Scan on groups (cost=164.82 size=3934 width=24)
Development Tree:
-----------------
QUERY: EXPLAIN
SELECT hosts.host,
passwords.login,
passwords.uid,
groups.grp,
passwords.gecos,
passwords.home,
passwords.shell
FROM hosts,
passwords,
groups
WHERE hosts.host_id = passwords.host_id AND
groups.host_id = passwords.host_id AND
groups.gid = passwords.gid;
NOTICE: QUERY PLAN:
Hash Join (cost=4309.91 size=40 width=108)
-> Nested Loop (cost=4291.52 size=40 width=92)
-> Seq Scan on groups (cost=160.82 size=3934 width=24)
-> Index Scan using passwords_host_id_key on passwords (cost=1.05
size=154973 width=68)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on hosts (cost=8.53 size=198 width=16)
-Ryan
Import Notes
Resolved by subject fallback
This is exactly what I need. My testing is very limited. I basically
test the functionality, but not real-world samples. I am still working.
I will let everyone know when I am done, and you can throw any queries
at it.
Was there a speedup with the new optimizer? Was the new plan faster?
The new optimizer uses 'cost' much more reliably. I hope our cost
estimates for various join types is accurate.
I have fixed the optimizer, and it is working properly again, and faster
too.Looks good Bruce.
Here are some explain results from the 6.4.2 release and the development tree.
Postgres 6.4.2:
---------------
QUERY: EXPLAIN
SELECT hosts.host,
passwords.login,
passwords.uid,
groups.grp,
passwords.gecos,
passwords.home,
passwords.shell
FROM hosts,
passwords,
groups
WHERE hosts.host_id = passwords.host_id AND
groups.host_id = passwords.host_id AND
groups.gid = passwords.gid;
NOTICE: QUERY PLAN:Merge Join (cost=30894.02 size=2358855 width=108)
-> Nested Loop (cost=20459.89 size=278240 width=84)
-> Index Scan using hosts_pkey on hosts (cost=13.90 size=198 width=16)
-> Index Scan using passwords_pkey on passwords (cost=103.26
size=154973 width=68)
-> Seq Scan (cost=20459.89 size=0 width=0)
-> Sort (cost=164.82 size=0 width=0)
-> Seq Scan on groups (cost=164.82 size=3934 width=24)Development Tree:
-----------------
QUERY: EXPLAIN
SELECT hosts.host,
passwords.login,
passwords.uid,
groups.grp,
passwords.gecos,
passwords.home,
passwords.shell
FROM hosts,
passwords,
groups
WHERE hosts.host_id = passwords.host_id AND
groups.host_id = passwords.host_id AND
groups.gid = passwords.gid;
NOTICE: QUERY PLAN:Hash Join (cost=4309.91 size=40 width=108)
-> Nested Loop (cost=4291.52 size=40 width=92)
-> Seq Scan on groups (cost=160.82 size=3934 width=24)
-> Index Scan using passwords_host_id_key on passwords (cost=1.05
size=154973 width=68)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on hosts (cost=8.53 size=198 width=16)-Ryan
--
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, Pennsylvania 19026
Bruce Momjian wrote:
This is exactly what I need. My testing is very limited. I basically
test the functionality, but not real-world samples. I am still working.
I will let everyone know when I am done, and you can throw any queries
at it.Was there a speedup with the new optimizer? Was the new plan faster?
The new optimizer uses 'cost' much more reliably. I hope our cost
estimates for various join types is accurate.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No. It's ok for netsloop only.
Vadim
Bruce Momjian wrote:
This is exactly what I need. My testing is very limited. I basically
test the functionality, but not real-world samples. I am still working.
I will let everyone know when I am done, and you can throw any queries
at it.Was there a speedup with the new optimizer? Was the new plan faster?
The new optimizer uses 'cost' much more reliably. I hope our cost
estimates for various join types is accurate.^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No. It's ok for netsloop only.
That is bad. Can you tell someone how to compute those, so perhaps they
can give us accurate numbers.
--
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, Pennsylvania 19026