Re: query takes 65 times longer if I add 1 column (explain attached)

"Dan Weber" <weberdan(at)gmail(dot)com> writes:
> I've got a query that takes a very long time to execute if I add an
> additional column to the SELECT clause.

What have you got work_mem set to?

I'm guessing that the case where it doesn't add the Materialize node is
because it estimates the size of the data-to-be-materialized as more
than work_mem, which means that Materialize would require a temp file,
which causes a fairly big jump in the estimated cost of materializing,
which makes the no-materialize plan look cheaper. The two plans have
nearly the same estimated cost so it wouldn't take much to make that
happen.

The fact that the estimates diverge so far from reality can no doubt be
blamed on the horridly bad rowcount estimates: 3 rows estimated vs
179 actual for table_one, and 128592 estimated vs 4202 actual for
table_two. Have you ANALYZEd these tables lately?

regards, tom lane