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
