Re: Massive performance differences

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:

[...]

I just imported a dump of (B) into (A) to be sure to have the
same data set. When I executed the query on (A) the first time, it was
as slow as on (B). But the next time it was significantly faster:

vvz_dev=> explain analyze select * from veranstaltung_original order by semester;
                                                             QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=587.08..600.74 rows=5467 width=229) (actual
time=3188.975..3192.020 rows=5467 loops=1)
    Sort Key: semester
    ->  Seq Scan on veranstaltung_original  (cost=0.00..247.67 rows=5467
width=229) (actual time=2.330..1587.832 rows=5467 loops=1)
  Total runtime: 3205.281 ms
(4 Zeilen)

vvz_dev=> explain analyze select * from veranstaltung_original order by semester;
                                                            QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=587.08..600.74 rows=5467 width=229) (actual time=95.061..98.143
rows=5467 loops=1)
    Sort Key: semester
    ->  Seq Scan on veranstaltung_original  (cost=0.00..247.67 rows=5467
width=229) (actual time=0.015..7.749 rows=5467 loops=1)
  Total runtime: 102.948 ms
(4 Zeilen)


This leads to the idea that it might be a caching problem.
How can I confirm this?

-- Andreas