Skip site navigation (1) Skip section navigation (2)

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

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: "'lockhart(at)fourpalms(dot)org'" <lockhart(at)fourpalms(dot)org>, "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: Re: Any optimizations to the join code in 7.1?
Date: 2001-04-28 06:08:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

-----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 
> 11.2. Instanteous results! Instantaneous.....

But it is possible, under many circumstances, for query optimization 
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 
choice of table order.

Presumably we will be interested in making these two forms of inner 
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

pgsql-hackers by date

Next:From: Sergey E. VolkovDate: 2001-04-28 08:59:42
Subject: Re: WAL feature
Previous:From: Ian Lance TaylorDate: 2001-04-28 03:45:25
Subject: Support for %TYPE in CREATE FUNCTION

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group