From: Tom Lane <tgl@sss.pgh.pa.us>
To: Seth Robertson <pgsql-performance@sysd.com>
Cc: pgsql-performance@postgresql.org, Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
Date: Tue, 21 Jan 2003 17:31:35 -0500
Message-ID: <13165.1043188295@sss.pgh.pa.us> (raw)
In-Reply-To: <200301212207.h0LM7Dt21080@winwood.sysdetect.com>
References: <200301212120.h0LLKn820463>
<20030121134242.Q84028-100000@megazone23.bigpanda.com>
<200301212207.h0LM7Dt21080@winwood.sysdetect.com>
Seth Robertson <pgsql-performance@sysd.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-performance@sysd.com, sszabo@megazone23.bigpanda.com
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
In-Reply-To: <13165.1043188295@sss.pgh.pa.us>
References: <200301212120.h0LLKn820463> <20030121134242.Q84028-100000@megazone23.bigpanda.com> <200301212207.h0LM7Dt21080@winwood.sysdetect.com> <13165.1043188295@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox