I have a large report that I run once a day. Under 6.5.3 it took just over
3hrs to run. Under 7.0 it is now taking 8 hours to run. No other changes
were made.
This is on RedHat Linux 6.2. A PIII 733 with 384MB Ram, and 2 IDE 7200 RPM
disks. One disk contains the Postgres directroy including the data
directory, and the other disk has everything else.
The Postmaster is started with these options: -i -B 1024 -N 256 -o -F
The report is being run on a backup server just after the database has been
loaded from a dump and 'vacuum analyse'd. There is practically nothing else
running on the box during the run.
The report creates four separate concurrent cursors. Each of the queries is
sorted by a customer number which is an index. The index is unique in the
customer table but not the others. For the other cursors it pops values as
needed to process data for the current customer number. There are also
other selects that are run for each customer order processed to retrieve its
line items. The report does not update the database at all, it is just
accumulating totals that will be written out when the report finishes.
Top tells me the front end process is using 5 to 10 percent of the CPU and
the back end is using 10 to 20 percent. The load average is about 1.0 and
the CPU is about 80% idle. I am prettry certain on 6.5.3 that the CPU usage
was much higher. Its almost like the new version has some sort of throttle
to keep one backend from saturating the system. Indeed the box is much more
responsive than it used to be while running this report.
Suggestions?