Re: Postgres 7.3.1 poor insert/update/search performance

In message <20030121134242(dot)Q84028-100000(at)megazone23(dot)bigpanda(dot)com>, Stephan Szabo writes:

On Tue, 21 Jan 2003, Seth Robertson wrote:

> The postgresql.conf file is default (my sysadmin nuked all of my
> changes when he upgraded to 7.3.1--grr) and there are some shared
> memory configs: kernel.sem = 250 32000 100 128, kernel.shmmax =
> 2147483648, kernel.shmmni = 100, kernel.shmmax = 134217728 The
> WAL is not seperated (but see below).

You almost certainly want to raise shared_buffers from the default (64?)
to say 1k-10k. I'm not sure how much that'll help but it should help
some.

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.

> A "vacuum analyze" is performed between the write phase and the
> read phase. However, for your analysis pleasure, here are the
> results of a full verbose analyze and some explain results (both
> before and after).

BTW: what does explain analyze (rather than plain explain) show?

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
seth=> explain analyze select accum from test where val = 5;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..0.00 rows=1 width=4) (actual time=94.55..94.55 rows=0 loops=1)
Filter: (val = 5)
Total runtime: 99.20 msec
(3 rows)

seth=> explain analyze update test set accum = accum + 53 where val = '5';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..0.00 rows=1 width=18) (actual time=31.95..31.95 rows=0 loops=1)
Filter: (val = 5::bigint)
Total runtime: 32.04 msec
(3 rows)

seth=> explain analyze insert into test values (5, 53);
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
Total runtime: 7.50 msec
(2 rows)

seth=> vacuum full verbose analyze test
seth-> ;
INFO: --Relation public.test--
INFO: Pages 541: Changed 1, reaped 539, Empty 0, New 0; Tup 18071: Vac 81930, Keep/VTL 0/0, UnUsed 0, MinLen 40, MaxLen 40; Re-using: Free/Avail. Space 3298208/3298176; EndEmpty/Avail. Pages 0/540.
CPU 0.03s/0.00u sec elapsed 0.02 sec.
INFO: Index test_pkey: Pages 355; Tuples 18071: Deleted 81930.
CPU 0.04s/0.41u sec elapsed 1.96 sec.
INFO: Rel test: Pages: 541 --> 98; Tuple(s) moved: 18046.
CPU 0.95s/0.42u sec elapsed 12.74 sec.
INFO: Index test_pkey: Pages 355; Tuples 18071: Deleted 18046.
CPU 0.02s/0.05u sec elapsed 0.31 sec.
INFO: Analyzing public.test
VACUUM
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 select accum from test where val = 2147483648;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=4) (actual time=0.11..0.11 rows=0 loops=1)
Index Cond: (val = 2147483648::bigint)
Total runtime: 0.16 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)

seth=> explain analyze insert into test values (6, 53);
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
Total runtime: 0.08 msec
(2 rows)

seth=> explain analyze insert into test values (2147483647, 53);
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1)
Total runtime: 0.33 msec
(2 rows)
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

-Seth Robertson