Re: query optimization scenarios 17,701 times faster!!!

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
helps.

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.


-----Original Message-----
From: Q@ping.be [mailto:Q@ping.be]
Sent: Thursday, April 24, 2003 2:08 PM
To: Robert Dyas
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] query optimization scenarios 17,701 times
faster!!!


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
required it.

So if you put your query in an other order, you will get a
different result.

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
tables.


Kurt