[GENERAL] Massive performance differences
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)? Thanks in advance! -- Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 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 ---(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
Re: [GENERAL] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Massive performance differences
On Tue, 2005-03-15 at 18:10 +0100, Andreas Hartmann wrote: explain analyze select * from veranstaltung_original order by semester; 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) ^^^ isn't this value (1936.68) suspiscious for a seq scan ? can a lot of dead tuples cause this? maybe VACUUM FULL ANALYSE time ? gnari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Massive performance differences
On Tue, 2005-03-15 at 12:18, Ragnar Hafsta wrote: On Tue, 2005-03-15 at 18:10 +0100, Andreas Hartmann wrote: explain analyze select * from veranstaltung_original order by semester; 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) ^^^ isn't this value (1936.68) suspiscious for a seq scan ? can a lot of dead tuples cause this? maybe VACUUM FULL ANALYSE time ? It's not unreasonable for the first run when the machine has to hit the hard drives, but if it's that slow on subsequent reads, then there's likely some problem. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
