pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (16+ messages)  latest in thread

Message-ID: <13165.1043188295@sss.pgh.pa.us>
Permalink:  ../13165.1043188295@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13165.1043188295@sss.pgh.pa.us

 · 

reply

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