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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
