Re: Performance tips

Andrew Perrin <andrew_perrin@unc.edu> writes:
> Well, here's the output from vmstat:
> aperrin@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