Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8BC6D4767CB for ; Tue, 21 Jan 2003 17:33:36 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 90559476B91 for ; Tue, 21 Jan 2003 17:31:34 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0LMVZ5u013166; Tue, 21 Jan 2003 17:31:35 -0500 (EST) To: Seth Robertson Cc: pgsql-performance@postgresql.org, Stephan Szabo Subject: Re: Postgres 7.3.1 poor insert/update/search performance In-reply-to: <200301212207.h0LM7Dt21080@winwood.sysdetect.com> References: <200301212120.h0LLKn820463> <20030121134242.Q84028-100000@megazone23.bigpanda.com> <200301212207.h0LM7Dt21080@winwood.sysdetect.com> Comments: In-reply-to Seth Robertson message dated "Tue, 21 Jan 2003 17:07:13 -0500" Date: Tue, 21 Jan 2003 17:31:35 -0500 Message-ID: <13165.1043188295@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/217 X-Sequence-Number: 864 Seth Robertson writes: > I'll try that and report back later, but I was under the (false?) > impression that it was primarily important when you had multiple > database connections using the same table. Definitely false. shared_buffers needs to be 1000 or so for production-grade performance. There are varying schools of thought about whether it's useful to raise it even higher, but in any case 64 is just a toy-installation setting. > seth=> explain analyze select accum from test where val = 5; > QUERY PLAN > ----------------------------------------------------------------------------------------------- > Seq Scan on test (cost=0.00..323.89 rows=1 width=4) (actual time=0.13..14.20 rows=1 loops=1) > Filter: (val = 5) > Total runtime: 14.26 msec > (3 rows) > seth=> explain analyze update test set accum = accum + 53 where val = '5'; > QUERY PLAN > --------------------------------------------------------------------------------------------------------------- > Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=18) (actual time=0.24..0.24 rows=1 loops=1) > Index Cond: (val = 5::bigint) > Total runtime: 0.39 msec > (3 rows) The quotes are important when you are dealing with BIGINT indexes. You won't get an indexscan if the constant looks like int4 rather than int8. regards, tom lane