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