I'm having a problem here. I'm using Postgres 7.0.3 on a FreeBSD 4.2-RELEASE
machine... it's a Pentium II/450 w/ 128MB of RAM (not nearly enough, but
there'll be an upgrade soon). Anyway, I have a data table, which currently
has around 146,000 entries, though it will grow to a few million eventually.
There is also config and prefs tables, which have 4-5 rows each. When I
execute the following command:
SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
ORDER BY d.ts DESC
LIMIT 1
...it takes an astounding 50 seconds to complete, CPU usage goes to about
85% Now, a simple...
SELECT *
FROM data
ORDER BY ts desc
LIMIT 1
...takes about 16-26 seconds - still sloooow, but not as bad as with the
table join. What's really causing the slowdown? ...should I just execute
the command differently? I'm trying to get the latest data in all three
tables.
Once the server has 768MB+ of RAM, is it possible to load the entire table
into memory? should speed things up considerably.
Thanks,
--
David Edmondson <[EMAIL PROTECTED]>
GMU/FA d-(--) s+: a18>? C++++$ UB++++$ P+>+++++ L- E--- W++ N- o K-> w-- O?
M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv-->! b DI+++ D+ G(--) e>* h!>+ r++ y+>++
ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
