2009/12/20 Robert Haas <robertmh...@gmail.com>: > On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada <umi.tan...@gmail.com> wrote: >> 2009/10/20 Andrew Gierth <and...@tao11.riddles.org.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers