Re: [HACKERS] Optimizer is fixed, and faster

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