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

Re: Fixing Grittner's planner issues

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Subject: Re: Fixing Grittner's planner issues
Date: 2009-02-12 02:01:03
Message-ID: 603c8f070902111801w52f0dae5l34e59922f99f625@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Feb 11, 2009 at 8:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [ forgot to respond to this earlier, sorry ]

Thanks for responding now.

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On a related note, I have some vague unease about planning A SEMI JOIN
>> B as A INNER JOIN (UNIQUE B), as make_one_rel currently attempts to
>> do.  For a merge join or nested loop, I don't see how this can ever be
>> a win over teaching the executor to just not rescan B.  For a hash
>> join, it can be a win if B turns out to have duplicates, but then
>> again you could also just teach the executor to skip the insertion of
>> the duplicate into the table in the first place (it has to hash 'em
>> anyway...).  I think maybe I'm not understanding something about the
>> logic here.
>
> The case where this is a win is where B is small (say a few rows) and
> not unique, and A is large, and there's a relevant index on A.  Then
> considering this join approach lets us produce a plan that looks like
>
>        NestLoop
>                HashAggregate (or GroupAggregate)
>                        Scan B
>                IndexScan A
>                        Index Condition : A.x = B.y

Right, so maybe I wasn't as clear as I could have been in asking the
question.  I do understand how it can be a win to unique B and use it
as the OUTER relation (jointype JOIN_UNIQUE_OUTER).  What I don't
understand is how it can ever be a win to unique B and use it as the
INNER relation (jointype JOIN_UNIQUE_INNER).

>> One thing I notice is that src/backend/optimizer/README should
>> probably be updated with the rules for commuting SEMI and ANTI joins;
>> it currently only mentions INNER, LEFT, RIGHT, and FULL.
>
> Yeah, I noticed that too.  How embarrassing.  Will fix it as part of
> the patch, which I hope to start on tomorrow.

Cool.  On the topic of documentation, I find the following comment in
joinrels.c rather impenetrable:

                        /*
                         * Do these steps only if we actually have a
regular semijoin,
                         * as opposed to a case where we should
unique-ify the RHS.
                         */

I don't think "regular semijoin" is a term of art, so I'm somewhat at
a loss to understand what this means.  And why "as opposed to" a case
where we should unique-ify the RHS?  ISTM the code will sometimes try
both...

...Robert

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-02-12 03:03:43
Subject: GIN fast insert database hang
Previous:From: Jeff DavisDate: 2009-02-12 01:48:16
Subject: Re: advance local xmin more aggressively

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