Re: Performance tips

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