From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> |
Cc: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, Greg Stark <gsstark(at)mit(dot)edu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: LATERAL |
Date: | 2009-12-19 18:11:36 |
Message-ID: | 603c8f070912191011x370d6827u56c3444bc2566895@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hitoshi Harada | 2009-12-19 18:40:47 | Re: LATERAL |
Previous Message | Hitoshi Harada | 2009-12-19 17:49:26 | Re: LATERAL |