2009/10/20 Andrew Gierth <andrew@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.
Regards,
--
Hitoshi Harada