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?