From: gurkan@resolution.com
To: pgsql-performance@postgresql.org
Subject: how to optimize postgres 8.1
Message-ID: <1159549965.451d540d3a997@www.resolution.com>
Date: Fri, 29 Sep 2006 13:12:45 -0400 (EDT)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: IMP/PHP IMAP webmail program 2.2.8

Hi,
I am working on datamigration from older version of informix to postgres 8.1

I need to increase performance on postgres, since informix(older version,older
hardware, little bigger DB data)
is 4-5 times faster than postgres8.1 (new hardware, less DB data)

My readings from Internet lead to me below configs but not making faster. I am 
doing this first time and hoped to get help from forum here.

I(we) am running 4GB ram running FC5(64bit), postgresql 8.1

My configs are
-------------------
kernel.shmmax = 1048470784
kernel.shmall = 16382356
-------------------
shared_buffers = 32768
work_mem = 16384
effective_cache_size = 200000
random_page_cost = 3
-------------------

If I run the query below with informix, it gives cost=107.
with postgres with additional indexes it gives cost=407, before the additional
indexes it was even much slower
------------------------------------------------------
development=# explain SELECT count (distinct invC.inv_id) as cnt FROM
inv_categories invC, inv_milestones invM, milestoneDef mDef, inv_milestones
invM2, milestoneDef mDef2 WHERE category_id = 1 AND invC.inv_id = invM.inv_id
AND mDef.id = invM.milestone_id AND mDef2.id = invM2.milestone_id AND
invM2.inv_id = invC.inv_id AND (mDef.description LIKE '7020%' OR
mDef.description LIKE '7520%') AND invM.dateDue <= CURRENT_DATE AND
(mDef2.description LIKE '7021%' OR mDef2.description LIKE '7521%') AND
invM2.dateDue >= CURRENT_DATE;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=407.37..407.38 rows=1 width=4)
   ->  Nested Loop  (cost=2.06..407.37 rows=1 width=4)
         ->  Nested Loop  (cost=2.06..398.21 rows=3 width=8)
               ->  Nested Loop  (cost=2.06..379.57 rows=1 width=8)
                     ->  Nested Loop  (cost=2.06..367.36 rows=4 width=12)
                           ->  Bitmap Heap Scan on inv_categories invc 
(cost=2.06..32.29 rows=18 width=4)
                                 Recheck Cond: (category_id = 1)
                                 ->  Bitmap Index Scan on az_test2 
(cost=0.00..2.06 rows=18 width=0)
                                       Index Cond: (category_id = 1)
                           ->  Index Scan using az_invm_invid on inv_milestones
invm2  (cost=0.00..18.60 rows=1 width=8)
                                 Index Cond: (invm2.inv_id = "outer".inv_id)
                                 Filter: (datedue >= ('now'::text)::date)
                     ->  Index Scan using milestonedef_pkey on milestonedef
mdef2  (cost=0.00..3.04 rows=1 width=4)
                           Index Cond: (mdef2.id = "outer".milestone_id)
                           Filter: ((description ~~ '7021%'::citext) OR
(description ~~ '7521%'::citext))
               ->  Index Scan using az_invm_invid on inv_milestones invm 
(cost=0.00..18.60 rows=3 width=8)
                     Index Cond: ("outer".inv_id = invm.inv_id)
                     Filter: (datedue <= ('now'::text)::date)
         ->  Index Scan using milestonedef_pkey on milestonedef mdef 
(cost=0.00..3.04 rows=1 width=4)
               Index Cond: (mdef.id = "outer".milestone_id)
               Filter: ((description ~~ '7020%'::citext) OR (description ~~
'7520%'::citext))
(21 rows)

------------------------------------------------------

Thanks for help.

-------------------------------------------------
This mail sent through IMP: www.resolution.com