I was hoping to point out an extreme example in hopes of showing that some
very important optimizations are not taking place.
In a perfect world, two different queries that must by definition return the
same result would run the exact same query plan, which would be the fastest
one available. Yes, I know I'm stating the obvious, but sometimes that
There are some basic optimizations that I think may not be taking place that
could have a dramatic impact in many different scenarios. The biggest one
that I can see could be stated like this: "if a where clause includes a
restriction on a primary key column with a fixed value, only that row should
be used in subsequent query processing"
Conceptually that seems like an "easy win" for improving performance,
possibly very significantly in a wide variety of circumstances.
From: Q(at)ping(dot)be [mailto:Q(at)ping(dot)be]
Sent: Thursday, April 24, 2003 2:08 PM
To: Robert Dyas
Subject: Re: [HACKERS] query optimization scenarios 17,701 times
On Wed, Apr 23, 2003 at 02:04:02PM -0400, Robert Dyas wrote:
> The following is a list of query pairs (one fast, one slow) that must
> produce identical results by definition (and do), but have very different
> execution times.
I think what you see is what is described in the documentation
too. If you use outer joins, it will do them in the order of the
query. I think the reason for that was that the SQL standard
So if you put your query in an other order, you will get a
I believe that they removed that restriction in the last/cvs
version of PosgresQL.
> Especially the last example.
Where it's joining alot of tables it doesn't use in the first
place. You even removed the conditions on how to join the
In response to
pgsql-hackers by date
|Next:||From: Stephan Szabo||Date: 2003-04-24 19:17:09|
|Subject: Re: query optimization scenarios 17,701 times faster!!!|
|Previous:||From: Bruce Momjian||Date: 2003-04-24 18:35:02|
|Subject: close() vs. closesocket()|