cont. how to optimize postgres 8.1 - Mailing list pgsql-performance

Hi,

How do I optimize postgres8.1?

I have 'vacuum full analyze'

I have posted output of 'explain analyze select ..'

I have created some indexes

I am running Mixed-Mode server,4GB ram running FC5(64bit), postgresql 8.1 AND
My configs are;(Are these good number?)
-------------------
kernel.shmmax = 1048470784
kernel.shmall = 16382356
-------------------
shared_buffers = 32768
work_mem = 16384
effective_cache_size = 200000
random_page_cost = 3
-------------------

-----------------------------------------------------------------------------------
development=# explain ANALYZE 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=499.93..499.94 rows=1 width=4) (actual time=8.152..8.154
rows=1 loops=1)
   ->  Nested Loop  (cost=65.26..499.92 rows=1 width=4) (actual
time=1.762..8.065 rows=13 loops=1)
         ->  Nested Loop  (cost=65.26..487.75 rows=4 width=8) (actual
time=1.637..7.380 rows=38 loops=1)
               ->  Nested Loop  (cost=65.26..467.71 rows=1 width=8) (actual
time=1.614..5.732 rows=13 loops=1)
                     ->  Nested Loop  (cost=65.26..455.53 rows=4 width=12)
(actual time=1.557..5.427 rows=13 loops=1)
                           ->  Bitmap Heap Scan on inv_categories invc
(cost=65.26..95.48 rows=18 width=4) (actual time=1.497..1.624 rows=44 loops=1)
                                 Recheck Cond: (category_id = 1)
                                 ->  Bitmap Index Scan on az_invcat_ifx1
(cost=0.00..65.26 rows=18 width=0) (actual time=1.482..1.482 rows=44 loops=1)
                                       Index Cond: (category_id = 1)
                           ->  Index Scan using az_invm_invid on inv_milestones
invm2  (cost=0.00..19.99 rows=1 width=8) (actual time=0.069..0.080 rows=0 loops=44)
                                 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.03 rows=1 width=4) (actual time=0.012..0.014 rows=1 loops=13)
                           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..19.99 rows=4 width=8) (actual time=0.023..0.110 rows=3 loops=13)
                     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.03 rows=1 width=4) (actual time=0.011..0.012 rows=0 loops=38)
               Index Cond: (mdef.id = "outer".milestone_id)
               Filter: ((description ~~ '7020%'::citext) OR (description ~~
'7520%'::citext))
 Total runtime: 8.466 ms
(22 rows)
-----------------------------------------------------------------------------------

thanks for help.

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