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(at)mascari(dot)com

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