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