From: | "Robert Dyas" <rdyas(at)adelphia(dot)net> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: query optimization scenarios 17,701 times faster!!! |
Date: | 2003-04-24 18:36:41 |
Message-ID: | MGEFJOBFIEAIADIKAMEKKEJJCIAA.rdyas@adelphia.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)ping(dot)be [mailto:Q(at)ping(dot)be]
Sent: Thursday, April 24, 2003 2:08 PM
To: Robert Dyas
Cc: pgsql-hackers(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-04-24 19:17:09 | Re: query optimization scenarios 17,701 times faster!!! |
Previous Message | Bruce Momjian | 2003-04-24 18:35:02 | close() vs. closesocket() |