I have one query which does not run very often. Sometimes it may be
months between runs.
However, when it does get executed, it scans approximately 100
identically-structured tables (a form of partitioning), extracts and
groups on a subset of the columns, and creates a new table. The
individual table queries have no where clauses, this is a full table
scan for every table.
I've tried all sorts of things to try to improve the performance,
which can take a /very/ long time.
We are talking about approximately 175GB of data before grouping/summarizing.
This is on PG 8.4.8 on Linux, 16GB of "real" RAM.
Most recently, I enabled trace_sort, disabled hash aggregation[1], and
set a large work_mem (normally very small, in this case I tried
anything from 8MB to 256MB. I even tried 1GB and 2GB).
In the logs, I saw this:
external sort ended, 7708696 disk blocks used: CPU 359.84s/57504.66u
sec elapsed 58966.76 sec
Am I to understand that the CPU portion of the sorting only took 6
minutes but the sort itself took almost 16.5 hours and used approx
60GB of disk space?
The resulting summary table is about 5GB in size as reported by \d+ in
psql (and pg_relation_size).
The underlying storage is ext4 on a hardware raid 10 with a BBU.
What sorts of things should I be looking at to improve the performance
of this query? Is my interpretation of that log line totally off base?
[1] if I don't disable hash aggregation and the work_mem is over 8MB
in size, the memory allocation explodes to the point where postgresql
wants dozens of gigs of memory. I've tried setting the statistics as
high as 1000 without benefit.
--
Jon
