From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jorge Daniel <elgaita(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Docker + postgreSQL : OOM killing in a large Group by operation |
Date: | 2018-04-05 17:27:07 |
Message-ID: | 14878.1522949227@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jorge Daniel <elgaita(at)hotmail(dot)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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry Sievers | 2018-04-05 18:39:01 | Re: PgUpgrade bumped my XIDs by ~50M? |
Previous Message | Alvaro Herrera | 2018-04-05 17:07:12 | Re: [PATCH] Logical decoding of TRUNCATE |