Re: LATERAL

2009/12/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
>> 2009/10/20 Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>:
>>> Right now, the only way pg can plan this is to do a hashjoin or
>>> mergejoin of the _entire content of big1 and big2_ and join the
>>> result against "small" (again in a hashjoin or mergejoin plan).
>>> This becomes excessively slow compared to the "ideal" plan:
>>>
>>>  nested loop
>>>      seqscan on small
>>>      nested loop
>>>         indexscan on big1 where id=small.id
>>>         indexscan on big2 where id=small.id (or big1.id which is equiv)
>>>
>>> (The same argument applies if "small" is not actually small but has
>>> restriction clauses)
>>
>> I have a similar issue on my mind, but is this the same as the topic?
>>
>> SELECT ... FROM small INNER JOIN (SELECT ... FROM large GROUP BY
>> large.id) agged ON small.id = agged.id WHERE small.id IN (bla bla bla)
>>
>> The ideal plan is SeqScan on small with filtering sub query aggregate
>> on large by small.id but the actual plan is full aggregate on large
>> since the planner doesn't push down outer qual to aggregate node. The
>> output will discard almost all of agged's output.
>
> I just tried this and it works for me.
>
> create table foo (id serial, name varchar, primary key (id));
> create table bar (id serial, foo_id integer references foo (id), name
> varchar, primary key (id));
> insert into foo (name) select random()::varchar from generate_series(1,1000);
> insert into bar (foo_id, name) select (g%10)+1, random()::varchar from
> generate_series(1,10000) g;
> explain select * from foo inner join (select foo_id, sum(1) from bar
> group by 1) x on foo.id = x.foo_id where x.foo_id = 1;
>
> ...Robert
>

Ah your example works for me, too. My issue is:

explain select * from foo inner join (select foo_id, sum(1) from bar
group by 1) x on foo.id = x.foo_id where foo.id = 1;

where foo.id = 1 (not where x.foo_id = 1).
And I now figured out it's another problem.

Regards,

--
Hitoshi Harada