From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Adam Rich <adam(dot)r(at)sbcglobal(dot)net> |
Cc: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Understanding sort's memory/disk usage |
Date: | 2009-09-27 16:57:35 |
Message-ID: | 25320.1254070655@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Adam Rich <adam(dot)r(at)sbcglobal(dot)net> writes:
> Please reference these explain plans. This is Pg 8.4.1
> http://explain-analyze.info/query_plans/4032-query-plan-2745
> http://explain-analyze.info/query_plans/4033-query-plan-2746
> First, could somebody explain what is leading the first query to choose
> a different plan that's much slower?
I think it's rejecting the HashAggregate plan because, with the
estimated-wider rows, the hash table is estimated to exceed work_mem.
> Second, why would it choose to sort on disk for what appears to be ~32MB
> of data, when my work_mem and temp_buffers are both 64 MB each?
The on-disk representation is more compact for various reasons.
> But it's only reporting 92kb of memory used? Why don't I see numbers
> between 64 MB and 128 MB for both the on-disk and in-memory plans?
You're not taking into account whether the sort is on pre-aggregation or
post-aggregation data.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-09-27 18:18:48 | Re: PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans |
Previous Message | Gerhard Wiesinger | 2009-09-27 16:05:51 | PostgreSQL reads each 8k block - no larger blocks are used - even on sequential scans |