Understanding sort's memory/disk usage

Поиск
Список
Период
Сортировка
От Adam Rich
Тема Understanding sort's memory/disk usage
Дата
Msg-id 4ABF1054.2000801@sbcglobal.net
обсуждение исходный текст
Ответы Re: Understanding sort's memory/disk usage  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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









В списке pgsql-general по дате отправления:

Предыдущее
От: Gerhard Wiesinger
Дата:
Сообщение: Re: Problem with data corruption and psql memory usage
Следующее
От: Ricky Tompu Breaky
Дата:
Сообщение: Re: How should the first step of PostgreSQL implementation should be?