Richard Huxton <email@example.com> writes: > There are also a lot of views involved here for very few output columns. > Tom - is the planner smart enough to optimise-out unneeded columns from > a SELECT * view if it's part of a join/subquery and you only use one or > two columns?
If the view gets flattened, yes, but I believe that it's not bright enough to do so when it can't flatten the view. You could tell easily enough by looking at the row-width estimates at various levels of the plan. (Let's see ... in Gaetano's plan the SubqueryScan is returning 12-byte rows where its input MergeJoin is returning 130-byte rows, so sure enough the view is computing a lot of stuff that then gets thrown away.) > Would I be right in thinking the planner doesn't materialise the > subquery because it's expecting 1 loop not 31? If there were 1 row the > plan would seem OK to me. Right; it doesn't see any predicted gain from the extra cost of materializing. But to me the main problem here is not that, it is that the entire shape of the plan would likely be different if it weren't for the "optimization fence" that the Subquery Scan node represents. I suspect too that the use of mergejoin as opposed to anything else within the vsp subplan is driven more by the need to produce sorted output than by what is the cheapest way to get the rows. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match