Re: Huge memory consumption on partitioned table with FKs

On Tue, Dec 1, 2020 at 12:25 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
> On Mon, Nov 30, 2020 at 9:48 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>> > Given that we're already looking at these checks, I was wondering if this
>> > might be the time to consider implementing these checks by directly
>> > scanning the constraint index.
>>
>> Yeah, maybe. Certainly ri_triggers is putting a huge amount of effort
>> into working around the SPI/parser/planner layer, to not a lot of gain.
>>
>> However, it's not clear to me that that line of thought will work well
>> for the statement-level-trigger approach. In that case you might be
>> dealing with enough tuples to make a different plan advisable.
>
> Bypassing SPI would probably mean that we stay with row level triggers, and the cached query plan would go away, perhaps replaced by an already-looked-up-this-tuple hash sorta like what the cached nested loops effort is doing.
>
> I've been meaning to give this a try when I got some spare time. This may inspire me to try again.

+1 for this line of work.

--
Amit Langote
EDB: http://www.enterprisedb.com