Re: Postgres 7.3.1 poor insert/update/search performance

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