On Wed, 8 Oct 2003, Neil Conway wrote:
> What is the query?
>
It retrieves an index listing for our boards. The boards are flat (not
threaded) and messages are numbered starting at 1 for each board.
If you pass in 0 for the start_from it assumes the latest 60.
And it should be noted - in some cases some boards have nearly 2M posts.
Index on board_name, number.
I cannot give out too too much stuff ;)
create or replace function get_index2(integer, varchar, varchar)
returns setof snippet
as '
DECLARE
p_start alias for $1;
p_board alias for $2;
v_start integer;
v_num integer;
v_body text;
v_sender varchar(35);
v_time timestamptz;
v_finish integer;
v_row record;
v_ret snippet;
BEGIN
v_start := p_start;
if v_start = 0 then
select * into v_start from get_high_msg(p_board);
v_start := v_start - 59;
end if;
v_finish := v_start + 60;
for v_row in
select number, substr(body, 0, 50) as snip, member_handle,
timestamp
from posts
where board_name = p_board and
number >= v_start and
number < v_finish
order by number desc
LOOP
return next v_row;
END LOOP;
return;
END;
' language 'plpgsql';
> Interesting (and surprising that the performance differential is that
> large, to me at least). Can you tell if the performance gain comes from
> an improvement in a particular subsystem? (i.e. could you get a profile
> of Sun/gcc and compare it with Sun/sunsoft).
>
I'll get these later today.
--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
