From: | Adam Rich <adam(dot)r(at)sbcglobal(dot)net> |
---|---|
To: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Understanding sort's memory/disk usage |
Date: | 2009-09-27 07:12:20 |
Message-ID: | 4ABF1054.2000801@sbcglobal.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
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? In the first plan only, this
expression is in the select & group by:
s.store_num || ' - ' || s.title
These are both non-null varchar fields. Both have a unique index.
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?
If I increase work_mem and temp_buffers to 128 MB, I get a faster plan:
http://explain-analyze.info/query_plans/4034-query-plan-2747
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?
Thanks,
Adam
From | Date | Subject | |
---|---|---|---|
Next Message | Ricky Tompu Breaky | 2009-09-27 08:36:33 | Re: How should the first step of PostgreSQL implementation should be? |
Previous Message | Gerhard Wiesinger | 2009-09-27 06:30:25 | Re: Sum of multiplied deltas |