Re: Speed issues - Mailing list pgsql-hackers-win32
From | Dru |
---|---|
Subject | Re: Speed issues |
Date | |
Msg-id | 40C66B88.5040004@treshna.com Whole thread Raw |
Responses |
Re: Speed issues
("Gary Doades" <gpd@gpdnet.co.uk>)
|
List | pgsql-hackers-win32 |
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.
pgsql-hackers-win32 by date: