Re: PG 7.0 is 2.5 times slower running a big report

Try putting 'nofsync' in your pg_options.

Bryan White wrote:
>
> 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?