RE: Re: Any optimizations to the join code in 7.1?

What would be nice, and I don't know how it would be done or what the 
syntax would be, would be a feature that allows PostgreSQL to skip 
not only the parsing stage, but the planning stage as well. Then, 
when the data has changed dramatically enough to warrant it, as you 
point out, a command can be issued to 'refresh' the query plan. My 
15-way join has expanded to a 19-way join and is still instantaneous, 
albeit on a very small set of data. Before 7.1, the query would 
simply have taken far too long, and I would have had to denormalize 
the database for performance purposes. With the explicit join syntax, 
it allows me to design the database 'the right way'. I basically used 
EXPLAIN SELECT... to determine the explicit join order, so as the 
data changes, its something I'll have to do on occassion to ensure 
good performance, but at least its now possible. :-)

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Thomas Lockhart [SMTP:lockhart@alumni.caltech.edu]
Sent:    Friday, April 27, 2001 9:49 PM
To:    mascarm@mascari.com; 'Tom Lane'
Cc:    'pgsql-hackers@postgresql.org'
Subject:    [HACKERS] 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

---------------------------(end of broadcast)---------------------  
------
TIP 4: Don't 'kill -9' the postmaster