Hi Martin,
fn(n) returns a number converted to a string (look at core/estring.h).
It is creating a postgresql parameter for binding.
I've also been looking at other uses of 'any' since I think they may
require changed too for performance (and to be consistent) but Arnt may
be better to say if this is the case. I'm making a patch set with those
changed too. However, the patch to selector may be enough for you just
now ?
Unfortunately I only have a live system to test with, so need to wait
until those users go offline in the evening to sneak in and then put
things back to normal after some tests.
I'll test again this evening (Europe) time and let you know how I get on.
Jim
On 12/05/2015 10:04, Martin Rode wrote:
> Hi Jim,
>
> how about this:
>
> root@enterprise:/usr/src/aox/server# diff selector.cpp
> selector_patched.cpp
> 1544c1544
> < return mm() + ".uid=any($" + fn( u ) + ")";
> ---
> > return mm() + ".uid=ANY(SELECT * FROM UNNEST($" + fn( u ) +
> "::integer[]))";
>
> I don't know how the binding works in AOX (whats this "fn" doing?),
> but it looks pretty straight forward.
>
> What ya say? I don't have the possibility to test it right away, as
> this is on a production system.
>
> Martin
>
>
> On 11.05.2015 17:23, NSS Ltd wrote:
>> Hi Martin,
>>
>> Thanks for confirming on the unnest. I'll look at the VALUES but the
>> issue there is how the parameters are bound - I'd hoped a patch could be
>> very small to achieve the result but because the prepared statements are
>> used, it was not so simple.
>>
>> The unnest looks like it is the simplest way to both use the single
>> prepared statement parameter and also build the temporary table for
>> performance.
>>
>> Arnt,
>>
>> I have another patch set based on the unnest which needs a little bit of
>> tidying up; I can send it probably later today. It does mean quite a
>> few things are changed. Do you have a test suite which can be used to
>> validate the changes ? I have a site I can test for short periods but
>> am cautious about putting something in which has not been regression
>> tested.
>>
>> Jim
>>
>>
>> On 11/05/2015 08:38, Martin Rode wrote:
>>> Jim,
>>>
>>> Thank you so much for spending time on this.
>>>
>>> First thing I have noticed is that you might got the syntax wrong in
>>> your VALUES() version, the correct syntax needs _one_ VALUES at the
>>> beginning and then the actual values only.
>>>
>>> I have tested your UNNEST variant too, it works equally fine.
>>>
>>> Best
>>> Martin
>>>
>>> On 09.05.2015 22:23, NSS Ltd wrote:
>>>> Martin,
>>>>
>>>> Testing, I see the syntax is more correctly :
>>>>
>>>> select mm.uid, mm.modseq, mm.message from mailbox_messages mm where
>>>> mm.mailbox=441 and not mm.deleted and mm.uid=any(select * from
>>>> unnest('{...}'::integer[]))
>>>>
>>>> I'm trying to find if this still uses the improved query plan.
>>>>
>>>> TIA
>>>>
>>>> Arnt,
>>>> I see this issue has come up before in the source code - injector.cpp,
>>>> line 768. The unnest or values variant may be a better solution than
>>>> making the huge list of parameters. I'm trying some variations but
>>>> don't have a 500,000 message mailbox to test with (over to Martin for
>>>> that!).
>>>>
>>>>
>>>>
>>>>
>>>> On 09/05/2015 17:37, NSS Ltd wrote:
>>>>> Martin,
>>>>>
>>>>> Can you please re-run the query but instead of using VALUES(x),
>>>>> can you
>>>>> alter the array to have an UNNEST clause :
>>>>>
>>>>> select mm.uid, mm.modseq, mm.message from mailbox_messages mm where
>>>>> mm.mailbox=441 and not mm.deleted and mm.uid=any(unnest('{...}'))
>>>>>
>>>>> Please tell me the performance/query plan on this compared with the
>>>>> original and with your VALUES() version.
>>>>>
>>>>> Thanks
>>>>> Jim
>>>>>
>
>
