Re: query planning different in plpgsql? - Mailing list pgsql-performance

On Mon, 26 Oct 2009 09:19:26 -0400, Merlin Moncure <mmoncure@gmail.com>
wrote:
> On Mon, Oct 26, 2009 at 6:05 AM, Michal J. Kubski <michal.kubski@cdt.pl>
> wrote:
>> On Fri, 23 Oct 2009 16:56:36 +0100, Grzegorz Jaśkiewicz
>> <gryzman@gmail.com> wrote:
>>> On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead
>>> <scott.lists@enterprisedb.com>wrote:
>>>
>>>>
>>>>
>>>>   Do you not have an index on last_snapshot.domain_id?
>>>>
>>>
>>> that, and also try rewriting a query as JOIN. There might be difference
>> in
>>> performance/plan.
>>>
>> Thanks, it runs better (average 240s, not 700s) with the index.
> Rewriting
>> queries
>> as JOINs does not make any difference.
>> The last_snapshot is a temp table created earlier in the procedure
>> and the query in question is preceded with CREATE TEMPORARY TABLE as
> well,
>> not a cursor.
>> I still do not get why it performs differently inside the procedure.
>> Is there any way to see what planning decisions were made?
> 
> not directly....can we see the function?
> 
> merlin

It looks like that (I stripped off some fields in result_rs record, to make
it more brief
and leave the relevant part) 

CREATE OR REPLACE FUNCTION build_list() RETURNS SETOF result_rs AS $$
DECLARE 
    start_time TIMESTAMP;
    rec result_rs;
BEGIN
    start_time := timeofday()::timestamp;

    CREATE TEMPORARY TABLE last_snapshot AS SELECT * FROM last_take; --
last_take is a view
    CREATE INDEX last_snapshot_idx ON last_snapshot USING btree(domain_id)
WITH (fillfactor=100);

    CREATE TEMPORARY TABLE tmp_lm AS SELECT m.domain_id, nsr_id FROM
nsrs_meta m JOIN last_snapshot l ON m.domain_id = l.domain_id;
    CREATE INDEX tmp_lm_idx ON tmp_lm USING btree(nsr_id) WITH
(fillfactor=100);

    CREATE TEMPORARY TABLE tmp_ns_bl_matching_domains AS SELECT DISTINCT
lm.domain_id FROM tmp_lm lm JOIN nsrs n ON lm.nsr_id = n.id JOIN ns_bl b ON
n.ip_id = b.ip_id;
    CREATE INDEX tmp_bls_0 ON tmp_ns_bl_matching_domains USING
btree(domain_id) WITH (fillfactor=100);
    DROP TABLE tmp_lm;

    CREATE TEMPORARY TABLE temp_result AS
        SELECT
            t.domain_id,
            t.name,
            (CASE WHEN b.domain_id IS NULL THEN 0 ELSE 1 END) AS is_bl,
            (CASE WHEN f.is_s IS NULL THEN 0 ELSE f.is_s::INTEGER END) AS
is_s,
        FROM last_snapshot t  
            LEFT JOIN tmp_ns_bl_matching_domains b ON
b.domain_id=t.domain_id
            LEFT JOIN (SELECT DISTINCT ON (domain_id) * FROM domain_flags
f) f ON t.domain_id=f.domain_id;

    FOR rec IN SELECT
            UTC_NOW(),
            name,
            is_bl,
            is_s
        FROM temp_result t
    LOOP
        RETURN NEXT rec;
    END LOOP;

    DROP TABLE temp_result;
    DROP TABLE tmp_ns_bl_matching_domains;

    PERFORM time_log('BUILD', get_elapsed_time(start_time));

END;
$$ LANGUAGE plpgsql;

Thanks,
Michal


-- 
I hear and I forget. I see and I believe. I do and I understand.
(Confucius)