Re: Massive performance differences

On Tue, 2005-03-15 at 11:10, Andreas Hartmann wrote:
> Hi all,
>
> I'm running the same database on two systems:
>
> A) Debian PostgreSQL 7.4.7
> B) SuSE PostgreSQL 7.3.4
>
> Both machines have approx. 1GHz and 1GB RAM.
> The amount of data is almost equal (+- 10%). But I'm facing
> huge performance differences. For instance, a simple sequential
> scan results in the following query plans:
>
>
> explain analyze select * from veranstaltung_original order by semester;
>
> A) ------------------------------------------------------------
>
> Sort (cost=734.74..747.77 rows=5210 width=232) (actual time=89.935..92.730
> rows=5210 loops=1)
> Sort Key: semester
> -> Seq Scan on veranstaltung_original (cost=0.00..413.10 rows=5210
> width=232) (actual time=0.011..7.852 rows=5210 loops=1)
> Total runtime: 96.900 ms
>
>
> B) ------------------------------------------------------------
>
> Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual
> time=2568.10..2573.02 rows=5467 loops=1)
> Sort Key: semester
> -> Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467
> width=223) (actual time=1936.68..2506.83 rows=5467 loops=1)
> Total runtime: 2579.08 msec
>
> ------------------------------------------------------------
>
> Could this be due to the different PostgreSQL versions?
> Is there a typical cause for such performance problems?
> How can I find out what's wrong with installation (B)?

I would expect your I/O subsystem and or kernel revision are more likely
the cause of differences here than the pg version. Note that the time
spent is in the seq scan, not the sort. Otherwise I'd worry about what
version of sort was being used.