Re: Performance tips

On Thu, 10 Jan 2002, Tom Lane wrote:

> Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:
> > shared_buffers is set to 128 in postgresql.conf - is that what you
> > mean?
>
> That's awfully small. On any modern machine you should be setting it in
> the low thousands. (Yeah, I know the default is only 64. We've had
> discussions about changing that ...) Note you must restart the
> postmaster to get this change to take effect.

Done - I've changed it to 3000 which is about as high as I can go without
recompiling the kernel (which I'll probably do in the future but don't
have time now).

>
> > HOWEVER... what I'm hearing from most folks is that, even under these
> > conditions, the performance I'm experiencing is worse than
> > expected. If that's true, what should I do to diagnose that?
>
> Have you shown us the specific query and the EXPLAIN output for it?
>
> regards, tom lane
>

auth=# EXPLAIN select count(patternid) from patterns where patternid in
(select
auth(# o_patternid from letters, pattern_occurrences where
letters.letterid =
auth(# pattern_occurrences.o_letterid and letters.datecat in (1,2));
NOTICE: QUERY PLAN:

Aggregate (cost=10770432787318.88..10770432787318.88 rows=1 width=4)
-> Seq Scan on patterns (cost=0.00..10770432756138.14 rows=12472297
width=4)
SubPlan
-> Materialize (cost=863548.43..863548.43 rows=5749731
width=12)
-> Hash Join (cost=1741.00..863548.43 rows=5749731
width=12)
-> Seq Scan on pattern_occurrences
(cost=0.00..250248.56 rows=15287556 width=8)
-> Hash (cost=1729.67..1729.67 rows=4530 width=4)
-> Seq Scan on letters (cost=0.00..1729.67
rows=4530 width=4)

EXPLAIN

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin(at)unc(dot)edu - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA