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(at)uci(dot)edu