Re: query planning different in plpgsql?

On Mon, 26 Oct 2009 11:52:22 -0400, Merlin Moncure <mmoncure(at)gmail(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)
>>

>> [..function cut off..]

> hm. what version of postgres are you using? I have some version
> dependent suggestions. Also, is it ok to respond to the list quoting
> any/all of your function? (I'd perfer to keep the discussion public if
> possible).
>

Hi,

Apologies for late response. It is 8.3.7. Tom Lane's suggestion to add
ANALYZE seem to help it,
though I still sometimes get long query runs.

Thanks,
Michal

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