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

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

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 (view raw or flat)
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


In response to

Responses

pgsql-hackers by date

Next:From: Stephan SzaboDate: 2003-04-24 19:17:09
Subject: Re: query optimization scenarios 17,701 times faster!!!
Previous:From: Bruce MomjianDate: 2003-04-24 18:35:02
Subject: close() vs. closesocket()

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