Обсуждение: how to optimize postgres 8.1

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

gurkan@resolution.com writes:
> I need to increase performance on postgres,

Well, for starters, have you ANALYZEd your tables?  That EXPLAIN output
looks suspiciously like default estimates.  Then post EXPLAIN ANALYZE
(not just EXPLAIN) results for your problem query.

> If I run the query below with informix, it gives cost=107.
> with postgres with additional indexes it gives cost=407,

That comparison is meaningless --- I know of no reason to think that
informix measures cost estimates on the same scale we do.  It'd be
interesting to see what query plan they use, though.

            regards, tom lane

Hi, Gurkan,

gurkan@resolution.com wrote:

> 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

What are your real timing measurements, in a produciton-like setup in a
production-like load? That's the only kind of "benchmarking" that will
give you an useful comparison.

You cannot compare anything else.

Especially, you cannot compare those "artificial" cost estimator values,
as they are likely to be defined differently for PostgreSQL and Informix.

For PostgreSQL, they are relative values to the cost of reading a page
as part of a sequential scan. And those values are tunable - fiddle with
the random_page_cost and cpu_*_cost values in the postgresql.conf, and
you will see very different values compared to the 407 you see now, even
if the query plan stays equal.

Do you look up the definition of cost for Informix? Have you made shure
that they're comparable?

HTH,
Markus


--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org