Sorry for the delay in the response. It took be a day to get 
everything upgraded to 7.1. To restate the problem -  in 7.0 with 
GEQO enabled, a 15-way join took 10 seconds. With GEQO disabled it 
took 18 seconds. 7.1 out of the box took only 2 seconds! I was amazed 
and shocked at this damned impressive improvement in planning 
speed....until I actually used the explicit JOIN syntax described in 
11.2. Instanteous results! Instantaneous.....

Thanks a bunch,
(still in shock)

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:	Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Sent:	Wednesday, April 25, 2001 12:42 PM
To:	mascarm@mascari.com
Cc:	'pgsql-hackers@postgresql.org'
Subject:	Re: [HACKERS] Any optimizations to the join code in 7.1?

Mike Mascari <mascarm@mascari.com> writes:
> I have a particular query which performs a 15-way join;

You should read
http://www.postgresql.org/devel-corner/docs/postgres/explicit-join  
s.html

			regards, tom lane



Fri, 27 Apr 2001 11:46:47 -0400
Mike Mascari <mascarm@mascari.com>
RE: Any optimizations to the join code in 7.1? 

You can thank Tom Lane for most/all of our optimization improvements.

> Sorry for the delay in the response. It took be a day to get 
> everything upgraded to 7.1. To restate the problem -  in 7.0 with 
> GEQO enabled, a 15-way join took 10 seconds. With GEQO disabled it 
> took 18 seconds. 7.1 out of the box took only 2 seconds! I was amazed 
> and shocked at this damned impressive improvement in planning 
> speed....until I actually used the explicit JOIN syntax described in 
> 11.2. Instanteous results! Instantaneous.....
> 
> Thanks a bunch,
> (still in shock)
> 
> Mike Mascari
> mascarm@mascari.com
> 
> -----Original Message-----
> From:	Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent:	Wednesday, April 25, 2001 12:42 PM
> To:	mascarm@mascari.com
> Cc:	'pgsql-hackers@postgresql.org'
> Subject:	Re: [HACKERS] Any optimizations to the join code in 7.1?
> 
> Mike Mascari <mascarm@mascari.com> writes:
> > I have a particular query which performs a 15-way join;
> 
> You should read
> http://www.postgresql.org/devel-corner/docs/postgres/explicit-join  
> s.html
> 
> 			regards, tom lane
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Fri, 27 Apr 2001 13:42:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Any optimizations to the join code in 7.1?
> ... 7.1 out of the box took only 2 seconds! I was amazed
> and shocked at this damned impressive improvement in planning
> speed....until I actually used the explicit JOIN syntax described in
> 11.2. Instanteous results! Instantaneous.....

But it is possible, under many circumstances, for query optimization to
be a benefit for a many-table query. The docs indicate that explicit
join syntax bypasses that, even for inner joins, so you may find that
this syntax is a net loss in performance depending on the query and your
choice of table order.

Presumably we will be interested in making these two forms of inner join
equivalent in behavior in a future release. Tom, what are the
impediments we might encounter in doing this?

                      - Thomas

Sat, 28 Apr 2001 01:49:09 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Any optimizations to the join code in 7.1?
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> But it is possible, under many circumstances, for query optimization to
> be a benefit for a many-table query. The docs indicate that explicit
> join syntax bypasses that, even for inner joins, so you may find that
> this syntax is a net loss in performance depending on the query and your
> choice of table order.

> Presumably we will be interested in making these two forms of inner join
> equivalent in behavior in a future release. Tom, what are the
> impediments we might encounter in doing this?

I don't think there are any real technical problems in the way; it's
simply an implementation choice not to treat INNER JOIN the same as an
implicit join list.  I did it that way in 7.1 mainly as a flyer, to see
how many people would think it's a feature vs. how many think it's a
bug.  The votes aren't all in yet, but here we have Mike apparently
pretty pleased with it, while I recall at least one other person who
was not happy with the 7.1 behavior.

			regards, tom lane

Mon, 30 Apr 2001 11:22:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: Any optimizations to the join code in 7.1? 