Обсуждение: Re: [HACKERS] Optimizer is fixed, and faster
> 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) -> IndexScan 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
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, Pennsylvania19026
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, Pennsylvania19026