Re: query planning different in plpgsql?

On Mon, 26 Oct 2009 09:19:26 -0400, Merlin Moncure <mmoncure(at)gmail(dot)com>
wrote:
> On Mon, Oct 26, 2009 at 6:05 AM, Michal J. Kubski <michal(dot)kubski(at)cdt(dot)pl>
> wrote:
>> On Fri, 23 Oct 2009 16:56:36 +0100, Grzegorz Jaśkiewicz
>> <gryzman(at)gmail(dot)com> wrote:
>>> On Fri, Oct 23, 2009 at 4:49 PM, Scott Mead
>>> <scott(dot)lists(at)enterprisedb(dot)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)