Re: surprisingly expensive join planning query

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: surprisingly expensive join planning query
Date: 2019-12-02 22:39:33
Message-ID: 20191202223933.sujdpatqryciqur4@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


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


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2019-12-02 22:52:31 Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Previous Message Tom Lane 2019-12-02 21:45:18 Re: Bogus EXPLAIN results with column aliases for mismatched partitions