From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Tomas Vondra <tv(at)fuzzy(dot)cz> |
Cc: | "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 17:29:05 |
Message-ID: | 374360640.5196069.1418318945338.JavaMail.yahoo@jws10094.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Dilger | 2014-12-11 17:31:26 | WIP patch for Oid formatting in printf/elog strings |
Previous Message | Heikki Linnakangas | 2014-12-11 17:22:44 | Re: 9.5 release scheduling (was Re: logical column ordering) |