From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | andrew_perrin(at)unc(dot)edu |
Cc: | Doug McNaught <doug(at)wireboard(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance tips |
Date: | 2002-01-10 04:36:29 |
Message-ID: | 18031.1010637389@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:
> Well, here's the output from vmstat:
> aperrin(at)hm269-26876:~/afshome/papers/authoritarian$ vmstat
> procs memory swap io system
> cpu
> r b w swpd free buff cache si so bi bo in cs us
> sy id
> 0 1 0 3052 2132 10460 413284 0 0 11 14 6 5 6
> 3 17
That's fairly useless, since what it gives you is the average values
since system boot. To get useful numbers, do "vmstat 5" (or some other
interval, but 5 seconds usually works well), and let it run long enough
to get a page's worth of output.
> I can't say I understand it though.... I've got a query running through
> psql that's been chugging away for nearly 2 hours now:
> auth=# select count(patternid) from patterns where patternid in (select
> o_patternid from
> auth(# letters, pattern_occurrences where letters.letterid =
> pattern_occurrences.o_letterid
> auth(# and letters.datecat in (1,2));
"WHERE ... IN (subselect)" queries are notoriously inefficient in Postgres.
It might be worth trying to recast as a WHERE ... EXISTS query. Also,
is the inner query likely to produce a lot of duplicates? If so,
changing it to a SELECT DISTINCT might help.
> Last time I tried to vacuum the database it was still hung 12 hours later
> so I cancelled.
Hmm, shouldn't take 12+ hours to vacuum a database with only ~8mil
records. How many indexes have you got in that thing? Some people
have been known to drop indexes, vacuum, recreate indexes.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2002-01-10 04:41:51 | Re: Performance tips |
Previous Message | Andrew Perrin | 2002-01-10 03:37:41 | Re: Performance tips |