From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Tomas Vondra <tv(at)fuzzy(dot)cz>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PATCH: hashjoin - gracefully increasing NTUP_PER_BUCKET instead of batching |
Date: | 2014-12-11 19:00:12 |
Message-ID: | CA+TgmoYJBfdMkNM++d+3SBEaT9B_MSh9K-cU0HoBN=t2xb7MpQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Dec 11, 2014 at 12:29 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Dec 6, 2014 at 10:08 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>>> select a.i, b.i from a join b on (a.i = b.i);
>>
>> I think the concern is that the inner side might be something more
>> elaborate than a plain table scan, like an aggregate or join. I might
>> be all wet, but my impression is that you can make rescanning
>> arbitrarily expensive if you work at it.
>
> I'm not sure I'm following. Let's use a function to select from b:
>
> create or replace function fb()
> returns setof b
> language plpgsql
> rows 1
> as $$
> begin
> return query select i from b;
> end;
> $$;
>
> explain (analyze, buffers, verbose)
> select a.i, b.i from a join fb() b on (a.i = b.i);
>
> I used the low row estimate to cause the planner to put this on the inner side.
>
> 16 batches
> Execution time: 1638.582 ms
>
> Now let's make it slow.
>
> create or replace function fb()
> returns setof b
> language plpgsql
> rows 1
> as $$
> begin
> perform pg_sleep(2.0);
> return query select i from b;
> end;
> $$;
> explain (analyze, buffers, verbose)
> select a.i, b.i from a join fb() b on (a.i = b.i);
>
> 16 batches
> Execution time: 3633.859 ms
>
> Under what conditions do you see the inner side get loaded into the
> hash table multiple times?
Huh, interesting. I guess I was thinking that the inner side got
rescanned for each new batch, but I guess that's not what happens.
Maybe there's no real problem here, and we just win.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2014-12-11 19:36:25 | Re: Commitfest problems |
Previous Message | Robert Haas | 2014-12-11 18:56:58 | Re: Commitfest problems |