pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Seth Robertson <pgsql-performance@sysd.com>
To: pgsql-performance@postgresql.org
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: Postgres 7.3.1 poor insert/update/search performance
Date: Tue, 21 Jan 2003 17:07:13 -0500
Message-ID: <200301212207.h0LM7Dt21080@winwood.sysdetect.com> (raw)
In-Reply-To: <20030121134242.Q84028-100000@megazone23.bigpanda.com>
References: <200301212120.h0LLKn820463>
	<20030121134242.Q84028-100000@megazone23.bigpanda.com>


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, 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



view thread (16+ messages)  latest in thread

Message-ID: <200301212207.h0LM7Dt21080@winwood.sysdetect.com>
Permalink:  ../200301212207.h0LM7Dt21080@winwood.sysdetect.com/
Also on:    postgresql.org/message-id/200301212207.h0LM7Dt21080@winwood.sysdetect.com

 · 

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: pgsql-performance@sysd.com, sszabo@megazone23.bigpanda.com
  Subject: Re: Postgres 7.3.1 poor insert/update/search performance
  In-Reply-To: <200301212207.h0LM7Dt21080@winwood.sysdetect.com>
  References: <200301212120.h0LLKn820463> <20030121134242.Q84028-100000@megazone23.bigpanda.com> <200301212207.h0LM7Dt21080@winwood.sysdetect.com>

* 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