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. 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
