Re: Docker + postgreSQL : OOM killing in a large Group by operation

Jorge Daniel <elgaita@hotmail.com> writes:
> I have a problem with a query that grabs a bunch of rows and then does an aggreate operation, at that moment it gots
killedby OOM-killer, I don't know why, the  engine starts using tmpfiles as expected , and then tries to work in
memoryand gots killed. 

> SELECT count(*)
>      FROM "changelog_change_transaction"
>      INNER JOIN "changelog_change_stats" ON ( changelog_change_stats.changelog_change_transactionid =
changelog_change_transaction.changelog_change_transactionid) 
>      LEFT OUTER JOIN "changelog_change_group" ON ( changelog_change_transaction.changelog_change_groupid =
changelog_change_group.changelog_change_groupid) 

>     WHERE ( changelog_change_group.companyid = 40 OR changelog_change_group.companyid = 1 OR
changelog_change_group.companyid= 53 OR changelog_change_group.companyid IS NULL ) 
>     AND changelog_change_transaction.started_at > '2017-04-21'
> GROUP BY  "changelog_change_transaction"."changelog_change_transactionid", "changelog_change_transaction"."epoch",
"changelog_change_transaction"
> ."changelog_change_groupid", "changelog_change_transaction"."started_at",
"changelog_change_transaction"."duration_microseconds","changelog_change_transaction"."changed_items",
"changelog_change_transaction"."xmin"
> ;

Why are you grouping on xmin?

> For sure if the GROUP BY the one that causes this OOM (when I removed it, the query finish ok ) , so I've change the
query-planto avoid the HashAggregate: 
>   But the explain still shows:

That's because type XID doesn't have sort support, only hash support,
so hash aggregation is the only way to do the query at all.

            regards, tom lane