Re: Postgres 7.3.1 poor insert/update/search performance

Seth Robertson <pgsql-performance(at)sysd(dot)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