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
