Re: Sun performance - Major discovery!

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 <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/