Re: surprisingly expensive join planning query

On Mon, Dec 02, 2019 at 03:21:47PM -0500, Tom Lane wrote:
>Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> On Sun, Dec 01, 2019 at 02:17:15PM -0500, Tom Lane wrote:
>>> LGTM.
>
>> Thanks. Do you think this is backpatch-worthy? I'm leaning to yes, but
>> maybe tweaking this just in master is fine. The query is somewhat
>> artificial and there are probably ways to rewrite it.
>
>I don't object to back-patching.
>

OK

>> The thing that annoys me a bit is that this fix is only partial. It gets
>> rid of maybe 80% of the allocations, but there's plenty of unnecessary
>> stuff left allocated ...
>
>Meh. I'm not that excited about getting rid of retail space wastage,
>unless there are single dominant points such as you found here. For
>small stuff it's far better to worry about memory context management.

I agree.

>(Speaking of which, I don't quite see why this would have been a problem
>once you got past geqo_threshold; the context resets that GEQO does
>should've kept things under control.)
>

Not sure I follow. geqo_threshold is 12 by default, and the OOM issues
are hapenning way before that.

It might be that one reason why this example is so bad is that the CTEs
have *exactly* the different join orders are bound to be costed exactly
the same I think.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services