Re: sun solaris & postgres

I figured out at least part of the problem...

Neil Conway writes:

 >Can you post the EXPLAIN results for the 30-sec/5-sec query on 7.0.3,
 >the EXPLAIN ANALYZE results for 7.2, and the relevant bits of the schema?

on att:
Text=> explain SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.00 rows=0 width=0)

EXPLAIN
Text=>

on steph:
Text=# explain analyze SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=12.74..132.06 rows=1 loops=1)
Total runtime: 132.23 msec

EXPLAIN
Text=#

on both att, steph, the table is set up as follows:

      create table citations
          (aid smallint, wid smallint,
           citation varchar(50),
           byteloc integer);

      create function get_citation (int2, int2, int4)
        returns SETOF varchar AS
        'select citation from citations where
           aid = $1 and wid = $2 and byteloc = $3'
        LANGUAGE 'SQL';

      truncate citations;
      copy citations from '/data/home/tlg/src/citegen/fullIds.txt';
      create index awbyte_idx on citations(aid,wid,byteloc);
      cluster awbyte_idx on citations;
      create index awcite_idx on citations(aid,wid,citation);
      vacuum citations;
      vacuum analyze citations;

I just found the problem, based in part on the explains.  After changing
the function to

      create function get_citation (int4, int4, int4)
        returns SETOF varchar AS
        'select citation from citations where
           aid = $1::smallint and wid = $2::smallint and byteloc = $3'
        LANGUAGE 'SQL';

on steph, the performance has pretty much improved to match that of att.
Would the changes between 7.0 and 7.2 account for this?  (There's no
explain analyze in 7.0, so I can't make a more detailed analysis of the
function on att.)  But now on steph:

Text=# explain analyze SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=6.83..6.87 rows=1 loops=1)
Total runtime: 7.00 msec

EXPLAIN


--
Cindy
ctmoore@uci.edu