> 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

Sun, 14 Feb 1999 16:18:02 -0700 (MST)
Ryan Bradetich <rbrad@hpb50023.boi.hp.com>
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@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

Sun, 14 Feb 1999 18:36:29 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Optimizer is fixed, and faster
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

Mon, 15 Feb 1999 09:23:10 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Optimizer is fixed, and faster
> 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

Sun, 14 Feb 1999 21:37:14 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Optimizer is fixed, and faster