Re: query decorrelation in postgres - Mailing list pgsql-hackers

From mahendra chavan
Subject Re: query decorrelation in postgres
Date
Msg-id 5a0dbeb90907240825g662834ceg80dbb88f073e4c2c@mail.gmail.com
Whole thread Raw
In response to Re: query decorrelation in postgres  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-hackers

Thank you for you response.  I was looking for a query rewriting mechanism which would be outside the optimizer and will do this kind of transformations at the query level.

~Mahendra


On Fri, Jul 24, 2009 at 7:32 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
mahendra chavan <mahcha@gmail.com> wrote:
> What I meant by de-correlation was optimizing a query to get rid of
> sub-queirs by using joins.
>
> eg. In the TPC-H schema, a query to find out the names of suppliers
> who supply parts having size  < 100
>
> *Query with nested subqueries:*
>
> SELECT
>     S_NAME
> FROM
>     SUPPLIER
> WHERE
>     S_SUPPKEY
>     IN (
>         SELECT
>             PS_SUPPKEY
>         FROM
>             PARTSUPP
>         WHERE
>             PS_PARTKEY
>                 IN (
>                     SELECT
>                         P_PARTKEY
>                     FROM
>                         PART
>                     WHERE
>                         P_SIZE < 100
>                 )
>
>
>
> *Query with joins without subqueries:*
>
>
> SELECT
>     S_NAME
> FROM
>     SUPPLIER
> INNER JOIN
>     PARTSUPP
> ON
>     S_SUPPKEY = PS_SUPPKEY
> INNER JOIN
>     PART
> ON
>     P_PARTKEY = PS_PARTKEY
> WHERE
>     P_SIZE < 100
>

Those two queries aren't exactly identical, because you could get
duplicate rows in the second which would not be there in the first.
Optimizations to "pull up" subqueries into a higher level FROM clause
as joins have been in PostgreSQL for as long as I've been using it,
but the ability to do the specific optimization you show there
(without the duplicates) was added in version 8.4 using "semi-joins".
I don't think any syntax was added to explicitly write a query using
semi-joins, though; it's an optimization of EXISTS, IN, ANY, etc.

To see how the planner chooses to execute a given query against a
particular schema which has a particular set of statistics about the
data distributions, use the EXPLAIN option.

http://www.postgresql.org/docs/8.4/interactive/sql-explain.html

-Kevin

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: display previous query string of idle-in-transaction
Next
From: Tom Lane
Date:
Subject: Re: bytea vs. pg_dump