Re: Postgres slowdown on large table joins

I didn't see you mention whether you had VACUUM ANALYZEd your database. That
alone will cause a huge boost in performance. You also didn't mention
indicies, but since they're SQL as opposed to db-specific, I will assume
that you know about those...

Greg

----- Original Message -----
From: "Dave Edmondson" <david(at)jlc(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, February 16, 2001 1:32 PM
Subject: Postgres slowdown on large table joins

> 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 <david(at)jlc(dot)net>
> 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
>