query slows under load - Mailing list pgsql-performance

Hi,

Sorry for the long e-mail.  Here is a summary of my questions:

I am running osdl-dbt1 against pgsql-7.3.3.  The result is at:
http://khack.osdl.org/stp/286627/

1. Based on the hardware and software configuration, does my database
configuration make sense?
2. Is 'defining a cursor and fetch multiple times' an efficient way to
implement a stored procedure?
3. index with desc/asc is not supported in PG, why it is not needed? Is
there any work-around?
4. I created a function to order the items, and created an index on that
function.  But the query did not pick up that index.  What did I miss?

Thanks,
=============

The I/O is light <10% disk utility, memory is 100% used, and CPU is
about 75%.  My goal is to increase CPU utilization to about 85% (without
swapping).  I've tried several database parameters and it did not make
much difference, I can get about 86 transactions/second.  Since the same
workload on SAPDB gives about 200 transactions/second, I must have
missed some important parameters.

So, the first question is:
Based on the hardware and software configuration, does my database
configuration make sense?

My statistics showed that one transaction is responsible for the bad
performance.  It takes 3-5 seconds to finish this transaction.  The
storedprocedure for this transaction executes select and fetches 20
times if there is record:

    OPEN refcur FOR SELECT i_id, i_title, a_fname, a_lname
                      FROM item, author
                     WHERE i_subject = _i_subject
                       AND i_a_id = a_id
                  ORDER BY i_pub_date DESC, i_title ASC;

    FETCH refcur INTO _i_id1, i_title1, a_fname1, a_lname1;
--    RAISE NOTICE ''%,%,%,%'', _i_id1, i_title1, a_fname1, a_lname1;

    IF FOUND THEN
      items := items + 1;
      FETCH refcur INTO _i_id2, i_title2, a_fname2, a_lname2;
    END IF;
    IF FOUND THEN
      items := items + 1;
      FETCH refcur INTO _i_id3, i_title3, a_fname3, a_lname3;
    END IF;
...
The second question is:
Is this the efficient way to implement?

The execution plan for the query is:
> explain analyze select i_id, i_title, a_fname, a_lname from item,
author where i_subject = 'ART' AND i_a_id = 1 ORDER BY i_pub_date DESC,
i_title ASC;
                                                         QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=33.95..34.57 rows=250 width=103) (actual time=0.44..0.44
rows=0 loops=1)
   Sort Key: item.i_pub_date, item.i_title
   ->  Nested Loop  (cost=0.00..23.99 rows=250 width=103) (actual
time=0.29..0.29 rows=0 loops=1)
         ->  Index Scan using i_i_subject on item  (cost=0.00..5.99
rows=1 width=64) (actual time=0.29..0.29 rows=0 loops=1)
               Index Cond: (i_subject = 'ART'::character varying)
               Filter: (i_a_id = 1::numeric)
         ->  Seq Scan on author  (cost=0.00..15.50 rows=250 width=39)
(never executed)
 Total runtime: 0.57 msec
(8 rows)

I think an index on item (i_pub_date desc, i_title asc) would help.  But
from reading the mailing list, PG does not have this kind of index, and
it is not needed (I could not find an answer for this).  Is there any
work-around?

I created an function to cache the order and created an index on it, but
the query did not pick it up.  Do I need to rewrite the query?

create or replace function item_order (varchar(60)) returns numeric(10)
as '
        DECLARE
                _i_subject alias for $1;
                _i_id numeric(10);
                rec record;
        BEGIN
                select i_id
                into _i_id
                from item
                where i_subject=_i_subject
                order by  i_pub_date DESC, i_title ASC;

                return _i_id;
        END;
'IMMUTABLE LANGUAGE 'plpgsql';

create index i_item_order on item (item_order(i_subject));

TIA,
--
Jenny Zhang
Open Source Development Lab
12725 SW Millikan Way, Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31