Re: Postgres 7.3.1 poor insert/update/search performance - Mailing list pgsql-performance

In message <20030121134242.Q84028-100000@megazone23.bigpanda.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,
MaxLen40; 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