Jorge Daniel <elgaita@xxxxxxxxxxx> 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 killed by OOM-killer, I don't know why, the engine starts using tmpfiles as expected , and then tries to work in memory and 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-plan to 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
