Re: Speed issues

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.



---------------------------(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