Ok so i've done some benchmarking to make certain that things are
actually slow.
Jason Sheets wrote:
>How much does performance drop, you mentioned significantly but didn't
>define it.
>
>
This select statement is a join across 3 tables.
select * from person,room,booking where person.id=booking.personid and
booking.roomid=room.id
There is about 1500 person and booking records, and 20 room records.
Total result from query is 3746 rows.
QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=51.31..403.84 rows=3278 width=896)
Hash Cond: ("outer".id = "inner".personid)
-> Seq Scan on person (cost=0.00..148.16 rows=1716 width=706)
-> Hash (cost=50.35..50.35 rows=382 width=190)
-> Hash Join (cost=1.63..50.35 rows=382 width=190)
Hash Cond: ("outer".roomid = "inner".id)
-> Seq Scan on booking (cost=0.00..37.27 rows=1527
width=89)
-> Hash (cost=1.50..1.50 rows=50 width=101)
-> Seq Scan on room (cost=0.00..1.50 rows=50
width=101)
If i remotely connect to the database on windows and run the sql
statement it takes less than a second. Likewise if i run the statement from
linux i also get less than a second. Though on the production machine
I get varied performance, it can take >10 seconds to run that sql statement.
Though on the test machine i get less than a second exection time.
After doing benchmarking on all the machines I belive a lot of the problems
may lie with windows and multitasking support. It could be swapping out
postgresql regularly when your running 2 programmes on the machine then
swapping it back in again. It may not be handling 2 processes running at
the sametime well. I think also running it things from cmd in windows
does take a serious performance hit, though i'm only using cmd for running
reports from. The only thing i'm worried about is if i install this on a
machine thats a few years old with 128MB of ram and it gets a massive
performance hit.
I'm going to try a database with 100000's of records later on this week
and i'll see if there is any major difference in performance then.
>What speed of CPU and how much memory do you have? Also how fast is your
>hard disk and how much memory is in use by Windows?
>
>
Development machine:
Pentium 4 3GHz, 1GB ram. Linux 2.6 with postgresql 7.4.1
Production machine:
Pentium 4 2.4GHz with 256MB of ram. winxp home
Test machine:
AMD XP 1600 Duron with 512MB of ram. win2k
The linux machine is the only one with 7200RPM drives.